Night Differential

In the Philippines, every employee is entitled to a Night Differential of Night shift pay of not less than 10% of his regular wage for each hour of work performed between 10 pm and 6 am.

How to compute night differential in excel?

A2 (TIME IN)
B2 (TIME OUT)
in C2 type this formula: =B2-A2
in D2 type this formula: =IF(AND(MOD(A2,1)< TIME(22,0,0),MOD(A2,1)> TIME(6,0,0)), TIME(22,0,0)-MOD(A2,1),0)
+IF(AND(MOD(B2,1)< TIME(22,0,0),MOD(B2,1)> TIME(6,0,0)),MOD(B2,1)-TIME(6,0,0),0)
E2 =C2-D2

Column C is the total hours, column D is standard/regular hours, column E night differential hours.
Format A&B as mm/dd/yyyy hh:mm, and C,D, & E as [h]:mm

=sumproduct(index((row(1:2880)>mod(a2,1)*1440)*(row(1:2880)<=round((mod(a2,1)+c2)*1440,)),)*index((row(1:2880)>1320)*(row(1:2880)<=1800)+(row(1:2880)<=360),))/1440

Originally Posted by tim201110

=sumproduct(index((row(1:2880)>mod(a2,1)*1440)*(row(1:2880)<=round((mod(a2,1)+c2)*1440,)),)*index((row(1:2880)>1320)*(row(1:2880)<=1800)+(row(1:2880)<=360),))/1440

It worked. You're awesome! Thanks, man!

Hi t
=(MIN(TIMEVALUE("6:00:00"),MOD(B2,1))+1)-MAX(TIMEVALUE("22:00:00"),MOD(A2,1))

Originally Posted by Sadath31

Hi t
=(MIN(TIMEVALUE("6:00:00"),MOD(B2,1))+1)-MAX(TIMEVALUE("22:00:00"),MOD(A2,1))

Hi Sadath31, I tried this formula. It is working when the time in is 11:00 PM and time out is 6:00 AM but does not work when I changed the time in to 1:00 AM and time out to 6:00 AM.