- last edited on
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.
02-02-2018 01:10 PM
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.
02-02-2018 01:48 PM - edited 02-02-2018 01:51 PM
I get a negative time which isn't even close to what it should be.
02-04-2018 01:05 AMSolution
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
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
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
to calculate next day in B12, just
Sheets ...(2) and ...(3) as an examples in attached.