Excel Scheduling - trying to calculate hours after midnight so they're not negative

Question

If anyone can give me any type of help on why I have negative numbers instead 40, which is what it should equal please help. I'd be so grateful. Thank You!

Answer 1

You correctly have "Use 1904 data system" selected for the workbook, in File > Advanced > "When calculating this workbook" section (scroll down to the end).

This is needed to allow times to calculate as negatives. (Try switching it off, and you will see the negs turn into all #s)

But, in B16 and C16 for example the time is simply entered at 18:00 and 01:00 without any date. 01:00 - 18:00 is indeed a negative time. Roughly -0.71 if displayed as a number.

The solution is to change each individual End-Start time calculation to cater for going past midnight. So replace "C16-B16" with "IF(C16-B16<0,C16-B16+1,C16-B16)"

You would need to do that for each day of the week, which makes the formula rather long.

Consider adding an extra column between days, to display the hours worked for that day. Then the total only needs to add up those cells.

Edit: beaten to it!

Edit again: you should use +1 as in my example, rather than +24 as in the previous post, because the unit is days, not hours.

Final edit: A much shorter solution is to replace "C16-B16" with "MOD(C16-B16,1)". This works by keeping only the fractional part of the time. With times, a decimal 1 is 24 hours.

Answer 2

The usual formula for going past 24 is:

 =EndTime - StartTime +(EndTime < StartTime)

To combine all that into one formula, for summing all the days of the week, is awkward but doable. It requires an array-entered formula as you need to test each pair individually.

The formula below makes use of your setup in that all of your EndTimes are in Even-Numbered columns; and the StartTimes are in Odd-numbered columns.

Note that the two constructed arrays are different by one column. In versions of Excel 2007+, you can test directly of ODD/EVEN with the ISODD/ISEVEN function.

This formula must be array-entered:

=SUM(
MOD(COLUMN($C16:$O16),2)*$C16:$O16-
(MOD(COLUMN($B16:$N16),2)=0)*$B16:$N16+
((MOD(COLUMN($C16:$O16),2)*$C16:$O16-(MOD(COLUMN($B16:$N16),2)=0)*$B16:$N16)<0))
*24

or, using ISODD and ISEVEN:

=SUM(
ISODD(COLUMN($C16:$O16))*$C16:$O16-
ISEVEN(COLUMN($B16:$N16))*$B16:$N16+
((ISODD(COLUMN($C16:$O16))*$C16:$O16-ISEVEN(COLUMN($B16:$N16))*$B16:$N16)<0))
*24

Or, even shorter, but harder to understand, since we use the MOD function to retain only the fractional component:

=SUM(
     MOD(
         ISODD(COLUMN($C16:$O16))*$C16:$O16-
         ISEVEN(COLUMN($B16:$N16))*$B16:$N16,
     1))
*24

---

To array-enter a formula, after entering the formula into the cell or formula bar, hold down ctrl-shift while hitting enter. If you did this correctly, Excel will place braces {...} around the formula.

I've done the formula for the line you show in your screenshot. The result is 37.9833 using your numbers

Answer 3

Your problem is when you go past midnight. Take for instance the first row on Friday, Saturday, and Sunday. Each day the ending time is 2:00 AM. For example on Friday, when you subtract the times, it takes 6:00 PM Friday minus 2:00 AM FRIDAY which is before the 6:00 PM.

In order to avoid the negative, but still keep the nice visual look of your chart, you should use an IF statement. To calculate for Friday use:

=IF(K3 < J3, K3 - J3 + 24, K3 - J3)

There are three sections in the IF statement.

(1) the condition

K3 < J3

This checks if K3 (the ending time) is less than J3 (the starting time). If so it would return a negative number (the time 2:00 AM is "less than" 6:00 PM).

(2) if condition is true

K3 - J3 + 24

If the condition is true, Excel uses this equation. I wrote it to add 24 to counteract any negatives that might occur.

(3) if condition is false

K3 - J3

If the condition is true, Excel uses this equation.

Summary

Use the IF statement provided in place of the equation you were using to calculate the hours for each day.

Answer 4

I had a similar issue calculating times that occasionally went past midnight with no day or date information. My start times were in the evening and would sometimes spill over into the next day, so I used a couple of formulas together to make it come out right for me:

=IF(D4< C4,TEXT(D4-C4+24,"h:mm"),TEXT(D4-C4,"h:mm"))

(note that I was forced to add a space after the less than symbol for some formatting reason here - I'm not using a space there in my formula)

It's a straightforward if, where in my case D4 is the end time and C4 is the start time. If end time is less than start time, it must have wrapped past midnight. The middle statement gives the total job time correcting for going past midnight (+24). The end shows the job time if regular subtraction is all you need. I found that without the "TEXT" and time format, the +24 math would display as a decimal value.