Convert Time Srtring to Number

[iamtrying]

I know the answer to this question has been posted, but I cant find it.

I have summed hours worked as a string in Crystal like 423:22 [hh.mm]. I would like to convert the string to a number to return sum values on the various groups of codes within the report.
See sample below. I would like to total each code group. I have tried the CTime function only to receive "Bad Time Format String".

Route 109-08
109-08

PL​

762:19​

109-08

RP​

45:45​

109-09

PL​

591:19​

109-09

RP​

45:18​

 

[pmax9999]

You will need to convert the Time "strings" into decimal numbers so you can add them.

The following code would do this for you:

[Code {@ConvertedTimes}]
Val(Split({Table.Field},':')[1]) + Val(Split({Table.Field},':')[2])/60
[/Code]

423:22 would then become 423.37 hours.

If you want to convert the sum of those records back into HH:MM format, you could use something like:

ToText(Truncate(Sum(@ConvertedTimes)), '#') + ':' + ToText((Sum(@ConvertedTimes) - Truncate(Sum(@ConvertedTimes))) * 60, '#')

Hope this helps.

Cheers
Pete

 

[Charliy]

Interesting, I do it the opposite way. I multiply the hours by 60 and add the minutes - then everything is minutes.

I've created functions that do all the conversion for me.

 

[iamtrying]

Thanks guys, the report is calculating properly with the code provided by pmax9999.

 

[SkipVought]

I personally prefer to do ALL time calculations in units of DAYS as that's what units are used in Date/Time.

Furthermore I'd be actually using a REAL Time value and displaying as hh:mm.