Subtracting Hours

[Mark256]

Hi

I have a query with two calculated fields that return the business hours between two sets of dates in the format "h:nn".

What I would like to do is subtract the first field [HoursA] from the second [HoursB], but the expression [HoursA] - [HoursB] doesn't work like it would in excel. I have also tried DateDiff("n", [HoursA], [HoursB]), but this only works if either field is less than 24 hours.

Does anyone have a expression for subtracting these calculated fields e.g. 20:00 - 5:00 = 15:00 hours?

[June7]

Need to convert the h:nn to decimal hours. So:

20:25 = 20.42

5:12 = 5.2

20.42 - 5.2 = 15.22 = 15:13

Review http://en.kioskea.net/faq/9089-vba-v...cimal-to-hours

Don't have to use VBA. The calcs can be done by expression in query. Instead of the Split() function from the first procedure, use other string functions like Left, Mid, InStr, Len to break up the time value.

[Mark256]

Thanks June7, but do you know how I can use this as an expression in the query?

[June7]

Like any expression in query.

Val([HoursA]) + Mid([HoursA], InStr([HoursA],":")+1, 2) / 60

[Mark256]

Like any expression in query.

Val([HoursA]) + Mid([HoursA], InStr([HoursA],":")+1, 2) / 60

Thank you June7!

I used this expression for the switch back to hours: CInt([yourfield]) & ":" & Format([yourfield] * 60 MOD 60, "00")