How to sum a duration that carries into the next day.

Hello all. I have a Numbers spreadsheet that calculates duty time (or hours worked) expressed in 24hour format. The issue I am finding is that when the 'Off Time' carries into the next day the formula returns a negative value. For example:

A3 has the following formula: =A2-A1

A1 = time on
A2 = time off
A3 = total time worked

so if

A1 = 19:00
A2 = 02:00
A3 = -17h (when it should total 7h)

Any ideas? Thank you in advance!!!

-Patrick

Can you enter the date? What I was thinking was to create a custom format with either the date in Mon DD, YYYY format, or the day of the year (number of the day) followed by the hour. That way the days should be in the accounting. I just tried it and it worked for me. The Customs I tested were Mon #, YYYY HH:MM (the spaces were important for readability), and MM/DD/YY HH:MM. Both worked properly. If you can't or don't want to enter days, then you could also try a conditional formula, IF(A2>A1, A2-A1,A2-A1+24). (NOTE: The conditional will still work if A2=A1, i.e. exactly 24 hours, but will fail if the total exceeds 24 hours, i.e., start Monday at 1900, end Wednesday at 0200. The formula will return 7, not 31.)

Thanks MacInWin! The conditional formula is almost exactly what I needed. However A3 returns 24 if A1 & A2 are blank. I need A3 to remain blank if A1 & A2 are blank. Any ideas on how to fix that part of the equation?

Nest an IF statement to put a blank in A3 if A1 is blank. If A1 is not blank, put the original formula. You may have to play with the formula a bit. I tried to create it and got a bit lost in the process. It's doable, just a bit tricky. If you populate A1 beforehand, you can make the blank check in A2. Or you can nest an OR statement to be blank if either A1 or A2 are blank. Again, a bit tricky, but doable.

IF(ISBLANK(B2),0,IF(C2>B2,C2−B2,C2−B2+24)) might be workable. I did a quick test and it worked for me. You can adjust the cell references, I was working in row 2, columns B and C. It puts a 0 if the first cell is blank, then if it isn't it does the next IF check and calculates the time. If the second cell is blank, it doesn't work, so you could add an OR statement in there to return a zero if either cell is blank. IF(OR(ISBLANK(B2),ISBLANK(C2),0,IF(C2>B2,C2−B2,C2−B2+24))) might work (I didn't test. The key is to get the parens in the right place).

Ok, tested this one and it works

IF(OR(ISBLANK(B2),ISBLANK(C2)),0,IF(C2>B2,C2−B2,C2−B2+24))

Basically, if b2 or c2 are blank, then it returns 0, otherwise it tests to see if C2 is greater than B2, and if so, subtracts them, otherwise it subtracts them but adds 24 for the over-midnight transition. You an edit to change the cell references to match your sheet, but be careful about the parens, it took me a while to get them right.