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 do you compute the total hours of night differential in Microsoft Excel? Please see the below formula. 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, and column E is the night differential hours. Format A&B as mm/dd/yyyy hh:mm, and C,D, & E as [h]:mm Here is another formula which is shorter than the previous one. A2 (TIME IN) B2 (TIME OUT) in C2 type this formula: =B2-A2 in D2 type this formula: =sumproduct(index((row(1:2880)>mod(a2,1)*1440)*(row...