SOLVED
Home

Combining IF and MOD functions

Highlighted
Daniel Sparks
New Contributor

Combining IF and MOD functions

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.

5 Replies

Re: Combining IF and MOD functions

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.

Re: Combining IF and MOD functions

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

Re: Combining IF and MOD functions

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

That results in 24.

 

Solution

Re: 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.

Re: Combining IF and MOD functions

Thank you Sergei. I can hide end date and it still works perfect.
Related Conversations
Cost estimator for small scale catering
Louisa Dira in Excel on
12 Replies
How to add the dates using the time criteria ?
Aditya Jadhav in Excel on
7 Replies
This calculation format driving me nuts
jjdrake in Excel on
6 Replies
Having an Issue with Excel hour/time formatting
Mike Button in Excel on
13 Replies
Need help with a conditional formatting equation
Daniel Desmarais in Excel on
7 Replies