Convert String to Hours or Number to allow Sum function

[IceRuby]

Crystal 10 with ODBC connection to SQL 2000 database.

Need to sum "Hours" field from table but is displaying as string value. Crystal errors with "bad time format string" when trying to convert.

Have tried functions ctime, tonumber etc. even totext and then converting but still errors.

Val function drops the minutes which I do still need as I need to sum total hours for any given day.

Data is entered by date by code and number of relevant hours

Code Hours
e.g. 15/1/06 REG 15:30
15/1/06 REGADJ 7:15
NEED TO TOTAL TO 22:45 (Or 22.75)

Very simple I'm sure I simply cannot seem to get around it!

Thanks for your help in advance.

 

[lbass]

If the hours field is a string, you could use a formula like:
(
val(split({table.hours},":")[1])*60 +
val(split({table.hours},":")[2])
)/60

You should then be able to right click on this formula in the detail section and insert a summary.

-LB

 

[MisterMo]

Or you can use CR functions like TimeValue:

This as the first formula to get the number of minutes

@TotMins

(hour(timevalue({table.hours}))*60) + (minute(timevalue({table.hours})))

then create another formula to get total hours to be placed at the end of the report

@TotHours

replace(replace(totext(truncate(Sum ({@TotMins}) /60)),".00","") + ":" +  replace(totext(remainder (Sum ({@TotMins}),60)),".00",""),":0",":00")

Mo