Combining IF and MOD functions

[Daniel Sparks]

I have a time sheet created where it can automatically calculate the hours. my problem is we work 24 hour shifts and when using MOD it gives me a 0.00 for hours worked. I would like to be able have it show 24 hours or higher if someone works over. The original MOD works up to 07:59 however, at times people work longer. I have attached the existing worksheet.

[#2]

Daniel,

if you are working with times greater 24 hours a good practice would be:

* Use Date and Time instead of just Time.

* Don't use MOD(D12-C12,1) because it calculates the remainder of 24 hours. Use (D12-C12)*24 instead.

[#3]

I get a negative time which isn't even close to what it should be.

[#4]

Did you use 11.02.2018  08:00:00 and 12.02.2018  08:00:00?

That results in 24.

[#5 · Accepted solution]

As a comment

1) I'm fully agree with Detlef what using date+time is most reliable solution. Using MOD is known trick for overnight shift, but it works only if the end time on next date is less than the start time on this date

2) On the other hand it's bit annoying  to enter both date and time every day. One workaround could be in case if you have ONLY 24 hours shifts, that means the shift is always overnight. In such case we may safely add 1 (one day) to end time and calculate hours as

=(D12+1-C12)*24

3) If that's not a case perhaps better to add one more column with End Date where by default

<End Date> = <Start Date> +1 

When hours are calculated as

=(D12+E12-B12-C12)*24

4) Not necessary to enter day of the week manually. It's enough to use, for example =B12 for the A12 (and so on) and apply custom format like "ddd" to the cells in column A

5) Also not necessary to use

=SUM(B11+1)

to calculate next day in B12, just

=B11+1

works fine.

Sheets ...(2) and ...(3) as an examples in attached.

[#6]

Thank you Sergei. I can hide end date and it still works perfect.