• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Night Differential Overtime Hours Excel Formula

Status
Not open for further replies.
K

Kristine Pacia

New Member
Help!

How to compute Night Differential Overtime hours in column N given the following criterias:
Night Differential Hours: 10:00 PM- 5:00 AM
Night Diffrential Overtime: Number of hrs that exceeded 8 hours of regular work that falls on Night Differential Hours.
 

Attachments

  • Schedule Template V2.xlsx
    52.1 KB · Views: 117
Welcome to the form. :)

Clarifications needed:

Your formula in M column is in conflict with your Night Differential Hours.
Code: 
=MOD(G9-F9,1)*24-(G9<F9)*(22-6)-MEDIAN(G9*24,22,6)+MEDIAN(F9*24,22,6)

Should it be 22:00 to 06:00? Or is the formula wrong?

For NDOT HRS, you need to calculate when OT starts and when it ends and calculate overlap with ND HRS.

However, how would you consider following case?

Time In @ 10:00 PM, Time Out @ 10:00 AM

OT is outside ND HRS, but since majority of shift fell on ND HRS (and started)... would 4 OT HRS here be considered NDOT HRS?

Or is it strictly any work hours that's over 8 hours in a given shift, that fell inside 22:00 to 05:00?
 
Hi yes it should be 22:00- 6:00 instead of 5.
For the Time In @ 10:00 PM, Time Out @ 10:00 AM 4 OT HRS are not considered as NDOT since the regular working hrs are the first 8 hrs of duty which is 10PM-6AM
 
Something like below then.
Code: 
=IF(MOD(G9-F9,1)*24>8,MOD(G9-(F9+8/24),1)*24-(G9<(F9+8/24))*(22-6)-MEDIAN(G9*24,22,6)+MEDIAN((F9+8/24)*24,22,6),0)
 
Is it not?

If it doesn't, it's due to float calculation.
Nest the formula in Round(formula, 2).
 
Status
Not open for further replies.
Back
Top