Convert Number to Time Field

[tsgsac]

New Member: New Crystal UserI have a formula that converts number to a time field however, it is not converting consistently correct. Some of the data is correct, no pattern when correct data is provided.

Example of data supplied:
91902 Should be 9:19:02AM
172352 Should be 5:23:52PM
13 Should be 12:00:13AM

Formula Utilized:
//change number to time:
stringvar x :=totext({dc_data.enroute_time},"000000");
time(val(left(x,2)),val(mid(x,3,2)),val(right(x,2)))

 

[lbass]

I think the formula is correct as is. You would have to format the time to display the way you wish.

For us to determine what is going on, we would have to see the original field, and then see how it displays incorrectly and then how you think it should display.

-LB

 

[fisheromacse]

I rewrote your formula and cannot find any problems with it either. It consistently returned a correct time for the #'s used.

I used:

numbervar ntt := {YourTable.NumberField};

stringvar nttx := totext(ntt,"000000");
time(val(left(nttx,2)),val(mid(nttx,3,2)),val(right(nttx,2)))

Can you show some sample data and how errors look in the report vs the original data?

Could you have inappropriate values in the database?

 

[tsgsac]

I have included sample page of my result, hope this helps.
Thank you.

occ_date case_type enroute_time enroute time to tim inservice_time

10/1/2008 120:0100:00AM 91902 9:28:14 am 93429
10/1/2008 120:0100:00AM 91902 9:19:02 am 121641
10/1/2008 120:0100:00AM 91902 9:19:08 am 102855
10/1/2008 120:0100:00AM 172352 5:23:52 pm 180139
10/1/2008 120:0100:00AM 172352 5:28:48 pm 173341
10/1/2008 120:0100:00AM 190652 7:17:50 pm 192139
10/1/2008 120:0100:00AM 190652 7:21:01 pm 195137
10/1/2008 120:0100:00AM 190652 7:17:41 pm 193823
10/1/2008 120:0100:00AM 190652 7:06:52 pm 210859
10/1/2008 120:0100:00AM 192406 7:24:06 pm 192451
10/1/2008 120:0100:00AM 192406 7:26:48 pm 210941
10/1/2008 120:0100:00AM 192406 7:24:06 pm 194830
10/1/2008 120:0100:00AM 192406 7:24:47 pm 192643
10/1/2008 120:0100:00AM 192406 7:40:56 pm 215408
10/2/2008 120:0100:00AM 1820 12:43:10 am 11124
10/2/2008 120:0100:00AM 1820 12:18:56 am 4123
10/2/2008 120:0100:00AM 1820 12:29:55 am 5140
10/2/2008 120:0100:00AM 1820 12:18:37 am 25622
10/2/2008 120:0100:00AM 2953 12:33:41 am 13320
10/2/2008 120:0100:00AM 2953 12:29:53 am 33643
10/2/2008 120:0100:00AM 2953 12:40:13 am 4046
10/2/2008 120:0100:00AM 2953 12:41:03 am 4340
10/2/2008 120:0100:00AM 11913 1:19:13 am 25620
10/2/2008 120:0100:00AM 11913 1:27:29 am 24256
10/2/2008 120:0100:00AM 11913 2:45:13 am 25941
10/2/2008 120:0100:00AM 11913 1:30:11 am 35156
10/2/2008 120:0100:00AM 25946 3:00:04 am 42358
10/2/2008 120:0100:00AM 25946 2:59:46 am 43012
10/2/2008 120:0100:00AM 31424 3:14:24 am 50511
10/2/2008 120:0100:00AM 31424 4:00:18 am 52222
10/2/2008 120:0100:00AM 31424 3:33:36 am 65214
10/2/2008 120:0100:00AM 201906 8:19:06 pm 214505
10/2/2008 120:0100:00AM 201906 8:21:23 pm 220859
10/2/2008 120:0100:00AM 234156 11:41:56 pm 630
10/3/2008 120:0100:00AM 2402 12:25:43 am 30554
10/3/2008 120:0100:00AM 2402 12:24:02 am 13010
10/3/2008 120:0100:00AM 2402 12:24:19 am 11211
10/3/2008 120:0100:00AM 22909 2:29:09 am 31950
10/3/2008 120:0100:00AM 22909 2:30:12 am 24008
10/3/2008 120:0100:00AM 22909 2:29:24 am 30943
10/3/2008 120:0100:00AM 13 12:04:08 am 1843

 

[lbass]

Are we supposed to be looking at columns two and three? Also, please show us the actual formula you are using. The formula you showed us would not return those values.

-LB

 

[tsgsac]

Columns three is the data and fourn is the column in which the formula is used.

Formula Utilized:
//change number to time:
stringvar x :=totext({dc_data.enroute_time},"000000");
time(val(left(x,2)),val(mid(x,3,2)),val(right(x,2)))

 

[lbass]

What report section is your sample data shown in?

There is something you are not telling us as the same formula would not return different results for the same number in the initial field.

-LB

 

[tsgsac]

The data is being shown in the Detail section of Crystal.

I am providing all information associated with this formula and result data.

formula:
//change number to time:
stringvar x :=totext({dc_data.enroute_time},"000000");
time(val(left(x,2)),val(mid(x,3,2)),val(right(x,2)))

 

[lbass]

Sorry, but the following data, e.g., (with three different results for the same input), could not possibly occur with that formula:

31424 3:14:24 am
31424 4:00:18 am
31424 3:33:36 am

I think that the result you are seeing is based on a formula that is taking into account the last column in some way and possibly comparing current value to previous or next--can't quite see it, but please look again at the particular formula to see what is going on.

-LB

 

[tsgsac]

LB,

Thank you for your efforts concerning my crystal issue. I went back and reviewed the data and found that the calculations are correct and the data in the enroute field is displaying incorrect. With that said now I can move forward to calculate the difference between inservice time and enroute time. I am trying to utilized diffdate in my formula is this the way to go when my field is time? How would I handle evt_reference CCHS2008608993 where the inservice_time is next day morning?

Sample:
evt_reference occ_date prime_or_backup enroute time to time inservice time to time inservice_date
CCHS2008607046 10/7/2008 12:00:00AM B 12:16:16AM 12:56:22AM 10/7/2008 12:00:00AM
CCHS2008607046 10/7/2008 12:00:00AM B 12:22:57AM 3:07:09AM 10/7/2008 12:00:00AM
CCHS2008608993 10/7/2008 12:00:00AM P 10:18:40PM 12:53:54AM 10/8/2008 12:00:00AM
CCHS2008608993 10/7/2008 12:00:00AM B 10:18:52PM 11:06:04PM 10/7/2008 12:00:00AM
CCHS2008609205 10/8/2008 12:00:00AM B 12:42:00AM 1:58:34AM 10/8/2008 12:00:00AM
CCHS2008609205 10/8/2008 12:00:00AM B 12:46:14AM 1:21:58AM 10/8/2008 12:00:00AM
CCHS2008609205 10/8/2008 12:00:00AM P 12:41:53AM 2:41:52AM 10/8/2008 12:00:00AM

 

[lbass]

I can't tell whether the dates here are datetimes that include the 12:00:00AM or whether they are separate fields. Could you just separate the fields in the labels and in the first row with "*" or something so I can see clearly? Thanks.

-LB

 

[tsgsac]

LB,

It would be confusing, The occ_date and inservice_date are date/time field however, the enroute_time_to_time and inservice_time_to_time are the fields that I created from the formula, those are time fields and what I need to get the elapsed time. The only reason I am looking at the date/time field is to discern when a call completes the next day. Does that help?

 

[lbass]

Use a datediff formula like this:

datediff("s",datetime(date({table.occdate}),{@enroutetimetotime}),datetime(date({table.inservicedate}),{@inservicetimetotime}))

Then you can convert this into a string format if you wish by using it as "dur" in the formula from faq767-3543.

-LB

 

[tsgsac]

LB

Thank you, Thank you! After several days of studying your reply I finally got it. This is what I used:

NumberVar dur:=datediff("s",datetime(date({dc_data.occ_date}),{@enroute time to time}),datetime(date({dc_data.inservice_date}),{@inservice time to time}));//get the seconds between 2 dates
NumberVar hrs;
NumberVar min;
NumberVar sec;
StringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec :=Remainder(dur,60);

hhmmss :=totext(hrs,"00")+":"+totext(min,"00")+":"+totext(sec,"00");

hhmmss

Now when I summerize on the elapsed time will I utilize the same formula in ther as well?

 

[lbass]

Set the summary = to "dur" and then use the rest of the formula as is.

-LB

 

[tsgsac]

lb

Works, but displays grand total or all subtotals as well. I inserted it in the format editor of the summary. Is that were I should have done it?


Formula entered:
NumberVar dur := Sum ({@elapsed});
NumberVar hrs;
NumberVar min;
NumberVar sec;
StringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec :=Remainder(dur,60);

hhmmss :=totext(hrs,"00")+":"+totext(min,"00")+":"+totext(sec,"00");

hhmmss

 

[lbass]

You have to add your group condition:

NumberVar dur := Sum ({@elapsed},{table.groupfield});

-LB

 

[tsgsac]

LB

Thank you, your help was wonderful!

Got just what I needed and opened the door to many possibilities.