Totalling hours from one day to the next

**Ducatisto** · 10-15-2008, 08:01 AM

Hello, I have a problem that I have so far failed to find an answer to. My company works 24/7 and with that some people on nights starting prior to midnight and then finishing after. Of course the "regular" formula works well in calculating between 00:00 onwards but I am getting an error when for instance the person starts at 20:00 and finishes at 04:00. I have tried numerous ways but still the error persists, any ideas? Thank you.

**MickG** · 10-15-2008, 08:22 AM

Hi, If start time in "A1" & Finish Time "B1" then:-

Please Login or Register  to view this content.

Might work
Regards Mick

**arthurbr** · 10-15-2008, 08:57 AM

Another possibility =(b1-a1+(b1<a1))*24

**Ducatisto** · 10-16-2008, 02:39 AM

Thank you Mick, your solution worked for me, that solved it correctly.

**daddylonglegs** · 10-16-2008, 05:08 AM

Originally Posted by MickG

=IF(A1>B1,24-A1+B1,B1-A1)

I see this formula posted quite often, unfortunately it doesn't give the correct answer if A1 and B1 contain actual times

It may appear to....

If A1 contains 20:30 and B1 04:30 and C1 the above formula and C1 is formatted as h:mm then you'll see 8:00 which is correct......but if you change the cell format to [h]:mm you see that the underlying value is 560:00. This means that if you use C1 in another calculation, to total hours for the week for instance, then the 560:00 figure is used and you get the wrong total

24 in the formula should be replaced with 1, i.e.

=IF(A1>B1,1-A1+B1,B1-A1)

or shorter

=B1-A1+(A1>B1)

or

=MOD(B1-A1,1)

Note: the original formula works when A1 and B1 show times in decimal format, e.g. if A1 shows 20.5 and B1, 4.5 then the formula gives the correct result in decimal hours, i.e. 8 [all cells formatted as number]