Feb 02 2018
12:06 PM
- last edited on
Jul 25 2018
10:57 AM
by
TechCommunityAP
Feb 02 2018
12:06 PM
- last edited on
Jul 25 2018
10:57 AM
by
TechCommunityAP
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.
Feb 02 2018 01:10 PM
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.
Feb 02 2018 01:48 PM - edited Feb 02 2018 01:51 PM
I get a negative time which isn't even close to what it should be.
Feb 02 2018 02:42 PM
Did you use 11.02.2018 08:00:00 and 12.02.2018 08:00:00?
That results in 24.
Feb 04 2018 01:05 AM
SolutionAs 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.
Feb 04 2018 02:33 PM
Feb 04 2018 01:05 AM
SolutionAs 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.