Night Differential And Night Differential OT Formula

[jameswan]

Hi everyone! I'm working on a time-tracking project in Excel and need help with formulas. I have Column A (Start Time) and Column B (End Time) in 24-hour format. I need formulas for the following:

1. ND: Calculate the total time worked during the night differential period (10:00 PM to 6:00 AM).
2. NDOT: Calculate the overtime hours worked during the night differential period (beyond the regular 8-hour work shift).

The times may span across midnight, so the formulas need to account for that. Any help or suggestion with this would be greatly appreciated. Thanks in advance!

 

[AliGW]

In AF2 copied down:

=IF(B2<A2,(1-MAX(22/24,A2))+MIN(6/24,B2),0)

You can work out NDOT yourself from this, as I am not sure what you want exactly.

 

[jameswan]

In AF2 copied down:

=IF(B2<A2,(1-MAX(22/24,A2))+MIN(6/24,B2),0)

You can work out NDOT yourself from this, as I am not sure what you want exactly.

Thank you so much for taking the time to help! I tried the formula, but it didn’t seem to work as expected. Could you please help me troubleshoot or clarify? I’d really appreciate any further guidance. Thanks again for your effort!

 

[AliGW]

You forgot to format the column as TIME!

If this isn't what you want, then you need to provide your expected results.

 

[jameswan]

Hi, AliGW. Thank you so much. I really appreciate your help! I’ve been testing it and noticed a small issue. For example, when Time In is 3:00 AM and Time Out is 1:00 PM, the ND (Night Differential) hours show 0, but it should reflect 3 hours (from 3:00 AM to 6:00 AM).

Would you mind helping me adjust the formula to account for this scenario? Thank you again for your time and effort!

 

[jameswan]

You forgot to format the column as TIME!

If this isn't what you want, then you need to provide your expected results.

I have created a new format and would appreciate your help. In this format, the hours from Time In (Column A) to Scheduled Time Out (Column D) that fall under the 22:00 - 06:00 range are considered Night Differential (ND). On the other hand, the hours from Scheduled Time Out (Column D) to Time Out (Column B) that fall under the 22:00 - 06:00 range are considered Night Differential Overtime (NDOT). I would really appreciate your input and assistance in refining this further if necessary. Thank you so so so much for your help.

​

 

[AliGW]

You have formulae in place, so I don't know what you want assistance with - sorry.

What is wrong with your current formulae? Highlight where you preceive that there are errors and tell us the result that you are expecting instead.

 

[jameswan]

You have formulae in place, so I don't know what you want assistance with - sorry.

What is wrong with your current formulae? Highlight where you preceive that there are errors and tell us the result that you are expecting instead.

Hi, AliGW. Apologies for the confusion, and thank you for your assistance so far. I currently have formulas in place for both the ND and NDOT columns, but they are not giving the correct results.

The main issue is that:

• ND should only count the hours from Time In (Column A) to Scheduled Time Out (Column D) that fall within the 22:00 - 06:00 range.
• NDOT should only count the hours from Scheduled Time Out (Column D) to Time Out (Column B) that fall within the same 22:00 - 06:00 range.

At the moment, the formulas seem to overlap or miscalculate these distinctions. Would it help if I provided the exact numbers I am expecting for each row? I’d be happy to do that if it makes things clearer.

Thank you again for your time and support!

 

[AliGW]

Would it help if I provided the exact numbers I am expecting for each row?

Yes! That's what I asked for! Add extra columns in the sample foile with your manually calculated expected results (NOT non-working formulae).

 

[jameswan]

Oh, I understand. Please refer to the attached file. I appreciate your effort and assistance, thank you!

 

[vletm]

jameswan​

What to do with Break Time with those ND and NDOT hours?
Can You use always time for those times ... as with sample?
... then it's possible to tell clear - are time in and time out in same day!

 

[AliGW]

I don't understand your expected results. How do you arrive at 4 in J3? Explain your logic and maths, please.

 

[vletm]

jameswan​

With my previous comments and Break Times ... something like this.
Your previous file has something mystic with NDOT-comment.

 

[jameswan]

jameswan​

What to do with Break Time with those ND and NDOT hours?
Can You use always time for those times ... as with sample?
... then it's possible to tell clear - are time in and time out in same day!

Thank you for raising that point! In this case, Break Time does not need to affect the calculation of ND or NDOT hours. The ND and NDOT hours should simply be counted based on the actual hours that fall within the 22:00 - 06:00 range, regardless of whether a break occurs during that period.

I will ensure all times (Time In, Scheduled Time Out, Time Out) are entered in your format to make it clear whether the shifts span the same day or cross over to the next.

If you think there's anything else that needs adjustment, I’d love to hear your suggestions. Thank you again for your help!

 

[jameswan]

I don't understand your expected results. How do you arrive at 4 in J3? Explain your logic and maths, please.

Thank you for asking! Here's how I calculated the 4 hours ND and 3 hours NDOT:

1. Night Differential (ND):
   • ND covers the hours from Time In (17:00) to Scheduled Time Out (02:00) that fall within 22:00 - 06:00.
   • From 22:00 to 02:00, there are 4 hours that qualify as ND.
2. Night Differential Overtime (NDOT):
   • NDOT covers the hours from Scheduled Time Out (02:00) to actual Time Out (05:00) that fall within 22:00 - 06:00.
   • From 02:00 to 05:00, there are 3 hours that qualify as NDOT.

Let me know if further clarification is needed, and thank you again for your help!

 

[AliGW]

ND:

=IF(A3<6/24,MIN(6/24,D3-INT(D3))-A3,IF(A3>D3-INT(D3),(1-MAX(22/24,A3))+MIN(6/24,D3-INT(D3)),0))

 

[vletm]

jameswan​

TimeIn & TimeOut should have full date with time to verify that there have marked all times.
... or how do You know if TimeIn is (Monday) 08:00 and TimeOut is (Wednesday) 17:00 ... Your way: 17:00 - 08:00 is nine hours or how?
... someway someone has missed one TimeOut & TimeIn 'marks'.
There are many other 'minor' things which would make these safer.

 

[AliGW]

ND:

=IF(A3<6/24,MIN(6/24,D3-INT(D3))-A3,IF(A3>D3-INT(D3),(1-MAX(22/24,A3))+MIN(6/24,D3-INT(D3)),0))

NDOT:

=IF(AND(A3>B3,D3<=1),MIN(1+6/24,(1+B3))-MAX(22/24,D3),IF(D3=B3,0,IF(AND(A3>B3,D3-INT(D3)<B3,D3-INT(D3)>=6/24,B3<=22/24),0,IF(AND(A3>=6/24,B3<=22/24,D3>B3,B3>A3),0,MAX(0,IF(AND(B3>D3,A3>B3)-INT(D3),B3-(D3-INT(D3))),0)))))

 

[jameswan]

jameswan​

TimeIn & TimeOut should have full date with time to verify that there have marked all times.
... or how do You know if TimeIn is (Monday) 08:00 and TimeOut is (Wednesday) 17:00 ... Your way: 17:00 - 08:00 is nine hours or how?
... someway someone has missed one TimeOut & TimeIn 'marks'.
There are many other 'minor' things which would make these safer.

Thank you for your feedback! You’ve raised some important points, and I can see how including full dates and ensuring all Time In and Time Out marks are accurate would make the data more reliable.

As I’m still a beginner in Excel, I’d really appreciate your suggestions on how best to handle these scenarios, especially for cases where Time In and Time Out span multiple days or where there might be missing data.

Your guidance would mean a lot to me as I work on improving this process. Thank you again for your patience and support!

 

[jameswan]

ND:

=IF(A3<6/24,MIN(6/24,D3-INT(D3))-A3,IF(A3>D3-INT(D3),(1-MAX(22/24,A3))+MIN(6/24,D3-INT(D3)),0))

NDOT:

=IF(AND(A3>B3,D3<=1),MIN(1+6/24,(1+B3))-MAX(22/24,D3),IF(D3=B3,0,IF(AND(A3>B3,D3-INT(D3)<B3,D3-INT(D3)>=6/24,B3<=22/24),0,IF(AND(A3>=6/24,B3<=22/24,D3>B3,B3>A3),0,MAX(0,IF(AND(B3>D3,A3>B3)-INT(D3),B3-(D3-INT(D3))),0)))))

It worked! Thank you so much for providing the correct formula! I really appreciate your help and expertise.

 

[vletm]

jameswan​

Few more steps ... or so:
There should be a plan.
There should be data, which shows, how something has done.
There should compare a plan and above.
Those should be see from on view.

 

[AliGW]

It worked! Thank you so much for providing the correct formula! I really appreciate your help and expertise.

You're welcome.

There should be data, which shows, how something has done.
There should compare a plan and above.
Those should be see from on view.

I have no idea what any of these statements mean, but I think that @vletm is probably trying to encourage you to change your approach to this. My formulae help you to get round failings in your data as it stands, which is far from optimal: going forward, you'd do well to follow some of the advice given here.

 

[vletm]

AliGW​

No Idea. I work on improving this process
I offer something before and after those formulas ... this jameswan's process.
You too gave formulas to one part.
Others should give solutions too even with improving details too.
I've commented only my solution.
Take care.

 

[AliGW]

I really don't know what you mean. I gave formulae for BOTH parts in post #18 and the OP said they worked in post #20.

As far as I can see, I have fully solved the OP's issue (without improving the process).

But as I said in my last post to the OP:

... going forward, you'd do well to follow some of the advice given here.

 

[jameswan]

I really don't know what you mean. I gave formulae for BOTH parts in post #18 and the OP said they worked in post #20.

As far as I can see, I have fully solved the OP's issue (without improving the process).

But as I said in my last post to the OP:

Hi AliGW & @vletm !

I’ve considered your suggestions about including the day and date—thank you so much for the great advice! I’ve implemented your feedback, and I would greatly appreciate it if you could review it to ensure everything looks accurate.

Additionally, I need further assistance with calculating approved NDOT. Specifically, these are the hours from Scheduled Time Out (Column G) to Approved Time Out for OT (Column N) that fall under the ND range (22:00 to 6:00). I’m trying to determine the approved OT (from Column L) that falls under the ND hours range (22:00 - 6:00). I would deeply value your expertise in helping clarify this part.

Your assistance and insights have been incredibly helpful so far. Thank you for your time and support!

Looking forward to hearing back from you.