• 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.

Regular Night Differential Hours and Night Differential OT

emewaw

New Member
Good Day!
How can I compute the number of hours between a specific range,
For example, an employee has timed in at 6:00 pm then timed out at 3:00 am the next day. What formula can I use to compute the number of hours that is only within the Night Differential, 10:00 pm to 6:00 am.
Note:
Night Differential can be changed based on the policy of the user.

Thank you very much.
 

Attachments

Chihiro

Excel Ninja
In E3:
=(MOD(D3-C3,1)-MOD(Shift!F5-Shift!E5,1))*24

Copy down.

In I3:
=MOD(D3-MAX(C3,Shift!G5),1)*24-(C3<Shift!E5)

Copy down.

It works for your sample. But I'm not sure what other variations are possible, since I don't know the labor law in you area.
Ex: In my area Day shift going over 14 hours is not permitted without allowing for 13 hour before next shift, and only with written consent.
 

emewaw

New Member
Thank you so much.

In my area, regardless of the shift, as long as your time goes between the given range of Night Differential (Mostly 10:00 pm to 6:00 am), rate will be different from the regular hour, also if you work for more than 8 hours, the excess will be considered as overtime, with a different rates.
So, if you have an overtime that goes between 10:00 pm to 6:00 am, then the rate will also be different from the regular overtime.
We'll again, Thank you. :)
 
Top