4

This is a screenshot of the problem I have and code I'm using.

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!

1
  • You might want to recalibrate your fingers. Cell P16, which you have highlighted, should be 38 (7+8+8+7+8). If you had realized that, you might have noticed that the value that you got, -10, was off by 48 hours, i.e., two days, corresponding to the two shifts that ended after midnight. Sep 6 '14 at 1:58
4

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.

1
  • 1
    I really like the mod solution, I've always used the if version to do this.
    – gtwebb
    Sep 5 '14 at 21:19
1

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

0

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.

1
  • As gtwebb says you should use +1 not +24, otherwise 23:00 to 07:00, for example, gives a result of 560:00! You can see that if you format result cell as [h]:mm. You would only use +24 when you are also multiplying the times by 24 to give decimal time. Sep 6 '14 at 8:36
0

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.

3
  • This really just duplicates solutions that were previously contributed.
    – fixer1234
    Jan 26 '17 at 18:56
  • I wouldn't have posted it if the other solutions worked for me. I was trying to solve the same challenge as the OP as far as I could see but didn't want to change to 1904 dates and the similar IF statement was not giving me expected results. Just trying to help. Jan 30 '17 at 13:50
  • This is quemeful's solution (with the same error), but using formatting as a fix. The time portion is the fractional part of the number. The integer portion is whole days. So the adjustment should be 1 instead of 24. Your formula gives the right time, but 23 days later. The formatting then ignores the wrong day and displays just the time. :-)
    – fixer1234
    Jan 30 '17 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.