Forum Discussion
Combining IF and MOD functions
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.
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.
I get a negative time which isn't even close to what it should be.
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.
- Thank you Sergei. I can hide end date and it still works perfect.
Did you use 11.02.2018 08:00:00 and 12.02.2018 08:00:00?
That results in 24.
