0

so I sorted out with the MOD function to calculate my worked hours where I used to start at XX PM/AM and finish at XX AM/PM where without the MOD function I could not calculate properly if I was starting like at 5PM and finish after midnight. Anyway, not it works properly if I finish working before midnight or after. But If I finish working exactly at midnight and put in the cell 12 AM value, it gives me as a result 0 using this formula: =IF(C41=0,0,(MOD((C41-B41)-D41,1))*24) But if I put as value 12 and press enter, it gives me the correct amount of worked hours, but it also changes the value to 12/01/1900 12:00:00 AM. Why is that? This is how the table is:

A       B           C           D       E               F
Date    Shift start Shift end   Break   Total x shift   Total
1-Oct   5:30 PM     12:00 AM            6.5             19
2-Oct   5:30 PM     12:00 AM            6.5 
4-Oct   12:00 PM     6:00 PM            6

And formulas are:

=IF(C41=0,0,(MOD((C41-B41)-D41,1))*24)

in every cell of E column.

Improve this question
3
  • 2
    All dates and times are stored as date plus time. If you store just a date, the time is midnight (decimal value of zero). If you store just a time, the date is January 0, 1900 (integer value of zero). If you want to do time arithmetic that involves midnight or times that straddle midnight, the simplest way is to store the actual date+time and use formatting to display just the time. Time math will work correctly if you do that.
    – fixer1234
    Commented Nov 1, 2015 at 15:28
  • While @fixer1234's answer is the best method, another down and dirty way I've used (and this can be done in a single excel if statement): if end - start is less than zero, then (we crossed midnight) hours = (end + 24 hours) - start else (we didn't cross midnight) hours = end - start.
    – Tyson
    Commented Nov 1, 2015 at 16:36
  • Guys, I'm not really into excel formulas. I managed to create that formula I am using now just by combining two examples I've found online, so I don't understand what are you talking about ..
    – Kerberos
    Commented Nov 3, 2015 at 6:42

1 Answer 1

Reset to default
0

I'm not sure which question to answer. You've got at least three separate questions in your OP. The changing of the value 12 to 12/01/1900 12:00:00 AM is simply how Excel works. You must have formatted the column as Date/Time, so it's converting the entered value to the default format specified in your settings. Depending on the version of Excel you're using, you could also format the column as simply Time, or modify the format to not show the date, as fixer1234 said.

Why you think MOD isn't working is hard to say, since you didn't explain it, but you're using it in a very odd fashion, and it should give you trouble if you pass it 0 (12am) as the first value. That means you're attempting to divide 0, which cannot be done. And you don't need it, anyway.

Your formula, similar to what Tyson indicated, can be simply 

=if((C1-B1)<0, C1+12-B1, C1-B1)

Note that you want to add 12 hours in the 'true' result, not 24 as Tyson stated. You will want to format columns B, C and D as your normal Time format, but for columns E and F you will probably want to eliminate the am/pm designators.

The results columns must remain formatted as Time because, otherwise, Excel will perform numerical math on the dateserial value that Excel is actually storing behind the scenes, instead of date math, and your results will be all wonky. For instance, 12am to 6:30am is 0.27 if your result column is formatted as a number field. Change it to Time format, and it correctly displays 6:30.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .