Splitting a field

[pumpkin31202]

I have a field that contains beginning and ending times. For example the field contains 1140-1240PM. I want to split the two times into two different fields so I can then do a caculation with them to determine the number of minutes there is between them. Can anyone help me do that?

 

[dgillz]

Will the field always by 11 characters ?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[ShravanAtHammerman]

Let's assume that the format is always the same and that it is a character field. Create two formula fields as follows:

//start time
numbervar hr := tonumber({table.field}[1 to 2]);
numbervar mn := tonumber({table.field}[3 to 4]);
time((hr*60 + mn)/1440)

//endtime
numbervar hr := tonumber({table.field}[6 to 7]);
numbervar mn := tonumber({table.field}[8 to 9]);
if {table.field}[10 to 11] = "PM" then hr := hr + 12;
time((hr*60 + mn)/1440)

create these to formulas and then subtract one from the other. The problem might be that the end time is in the next day and therefore is less then the start time. If this is a problem, please call. I would be glad to help.

SE



Hammerman Associates, Inc.

http://www.hammerman.com

Crystal, Training, Consulting, Support
800-783-2269, shravan@hammerman.com

 

[pumpkin31202]

Unfortenately, the field can be up to 13 characters.

 

[dgillz]

Pumpkin,

Please give several examples of how this field can be populated. Also, what is the minimum length of the field? Will it always be at least 11?

Basically we need more info to answer your question.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[pumpkin31202]

The field contains visit times. Some examples are 800-920; 1000-1110; 1105-1220PM; 125PM-230PM; 1240PM-120PM.

Whenever the time is in the afternoon or evening the system will place PM after it. The systems never includes AM. The smallest the field can be is 7 and the largest is 13.

 

[Naith]

Pumpkin,

Try this out. Note that you'll need version 8 or better to make use of the StrReverse function.

WhilePrintingRecords;

StringVar YourField;
StringVar Array TimeSplitter;
NumberVar SecondsDifference;
NumberVar Hours;
NumberVar Mins;
StringVar Difference;

YourField := {Table.Field};  

TimeSplitter := Split(YourField,'-');

If UBound(TimeSplitter) <> 2
Then &quot;Bad Data&quot;;

If UBound(TimeSplitter) = 2
Then
    If InStr(TimeSplitter[1],'PM') > 0
    Then TimeSplitter[1] := Left(StrReverse(TimeSplitter[1]),4) 
    + &quot;:&quot; + StrReverse(Left(TimeSplitter[1],(Length(TimeSplitter[1])-4)))
    Else TimeSplitter[1] := Left(StrReverse(TimeSplitter[1]),2)
    + &quot;:&quot; + StrReverse(Left(TimeSplitter[1],(Length(TimeSplitter[1])-2)));

If UBound(TimeSplitter) = 2
Then
    If InStr(TimeSplitter[2],'PM') > 0
    Then TimeSplitter[2] := Left(StrReverse(TimeSplitter[2]),4) 
    + &quot;:&quot; + StrReverse(Left(TimeSplitter[2],(Length(TimeSplitter[2])-4)))
    Else TimeSplitter[2] := Left(StrReverse(TimeSplitter[2]),2)
    + &quot;:&quot; + StrReverse(Left(TimeSplitter[2],(Length(TimeSplitter[2])-2)));

TimeSplitter[1] := StrReverse(TimeSplitter[1]);
TimeSplitter[2] := StrReverse(TimeSplitter[2]);

If Left(TimeSplitter[1],2) = '12' and
InStr(TimeSplitter[1],'PM') = 0
Then TimeSplitter[1] := Replace(TimeSplitter[1],'12:','00:');

If Left(TimeSplitter[2],2) = '12' and
InStr(TimeSplitter[2],'PM') = 0
Then TimeSplitter[2] := Replace(TimeSplitter[2],'12:','00:');

SecondsDifference := DateDiff('s',CurrentDate+TimeValue(TimeSplitter[1]),CurrentDate+TimeValue(TimeSplitter[2]));

Hours := Truncate(Truncate(SecondsDifference/60)/60);
mins := Remainder(Truncate(SecondsDifference/60),60);

Difference := ToText(Hours, &quot;0&quot;) + &quot;:&quot; + ToText(Mins, &quot;00&quot;);

Difference;

Naith

 

[pumpkin31202]

Naith,
Your formula worked. Thanks a lot. The only thing is now I need to sum the hours we just calculated, but the field is non-numeric. Any suggestions on that?
Thanks,
Pumpkin

 

[Naith]

Leave the output in seconds, i.e. remove everything after the SecondsDifference assignment, and total that.

Naith

 

[Reebo99]

Naith,

You do like your complicated formulas don't you,

I would have used :

StringVar StrTime := {StringField};
StringVar StrTime1 := Left(StrTime,instr(StrTime,"-&quot-1);
StringVar StrTime2 := Mid(StrTime,instr(StrTime,"-&quot+1);

TimeVar Time1 := If Len(StrTime1) in [4,6] then
time(picture(StrTime1,"xx:xxxx&quot) else
If Len(StrTime1) in [3,5] then
time(picture(StrTime1,"0x:xxxx&quot);

TimeVar Time2 := If Len(StrTime2) in [4,6] then
time(picture(StrTime2,"xx:xxxx&quot) else
If Len(StrTime2) in [3,5] then
time(picture(StrTime2,"0x:xxxx&quot);

DateDiff("s",CurrentDate+Time1,CurrentDate+Time2)

to get the difference in seconds.

The Time() will allow AM or PM as a valid Time string and the Picture() function will allow the last 2 x in "xx:xxxx" to not exist. The only possible problem was the leading 0 not being there for 125, but :

Len("125&quot = 3
Len("0125&quot = 4
Len("125PM&quot = 5
Len("0125PM&quot = 6




Reebo
Scotland (Sunny with a Smile)

 

[Naith]

Reebo,

Aside from the fact that that will give incorrect data for records between 12:00 and 12:59, that's a good post.

Naith

 

[Reebo99]

Whoops, schoolboy error! :

StringVar StrTime := {StringField};
StringVar StrTime1 := Left(StrTime,instr(StrTime,"-&quot-1);
StringVar StrTime2 := Mid(StrTime,instr(StrTime,"-&quot+1);

f Not(right(StrTime1,2) = "PM&quot and left(StrTime1,2) = "12" then
StrTime1 := "00"&mid(StrTime1,3);
If Not(right(StrTime2,2) = "PM&quot and left(StrTime2,2) = "12" then
StrTime2 := "00"&mid(StrTime2,3);

TimeVar Time1 := If Len(StrTime1) in [4,6] then
time(picture(StrTime1,"xx:xxxx&quot) else
If Len(StrTime1) in [3,5] then
time(picture(StrTime1,"0x:xxxx&quot);

TimeVar Time2 := If Len(StrTime2) in [4,6] then
time(picture(StrTime2,"xx:xxxx&quot) else
If Len(StrTime2) in [3,5] then
time(picture(StrTime2,"0x:xxxx&quot);

DateDiff("s",CurrentDate+Time1,CurrentDate+Time2)


Thanks Naith, as always you were there to point out my error. Is this better?

Reebo
Scotland (Sunny with a Smile)

 

[Naith]

A+