Calculate time difference between two dates excluding Sunday and 1/2 day for Saturday

[Shankar Narayanan T]

Hi,

I want to calculate the time difference between the two dates excluding Sunday. Total works hours per day is 8 hours starting from 9:30 a.m. to 5:30 p.m and during saturday it is 9:30 a.m. to 1:30 p.m.

I have written this formula..but it is not working properly.

=NETWORKDAYS.INTL(A6,B6,11,F$11:F$20)*8-(IF(AND(WEEKDAY(A6)>1,HOUR(A6)+(MINUTE(A6)/100)>9.3,NETWORKDAYS.INTL(A6,A6,11,F$11:F$20)>0),IF(HOUR(A6)+(MINUTE(A6)/100)>=17.3,8,(TIME(HOUR(A6),MINUTE(A6),0)-TIME(9,30,)))*24))-(IF(AND(WEEKDAY(B6)>1,HOUR(B6)+(MINUTE(B6)/100)<17.3,NETWORKDAYS.INTL(B6,B6,11,F$11:F$20)>0),IF(AND(WEEKDAY(B6)=7,HOUR(B6)+(MINUTE(B6)/100)>13.3),4,IF(HOUR(B6)+(MINUTE(B6)/100)<=9.3,8,(TIME(17,30,)-TIME(HOUR(B6),MINUTE(B6),0))*24))))

Where A6,B6 are start and end date & time, F11:F20 are holidays

Could anyone help me out to correct this formula

Regards,
Shankar

 

[NARAYANK991]

Hi Shankar ,

This is the problem with such monster formulae ; no one , probably not even the originator , might be interested in taking a relook at them !

If you can do it , use helper columns , and develop a formula , you and anyone else for that matter , can understand and maintain.

Narayan

 

[Lori]

Narayank is correct that the clearest way to do this would be to list all dates on a separate sheet with holidays and hours worked next to each one and sum these up. But if you want to do this using a single formula here is one possibility (not too thoroughly tested)...

Suppose you want to find work hours between:

B2 = 6-jun 10:00
C2 = 9-jun 16:00

You could split into separate cases {weekdays,saturdays} and {start;middle;end} and sum the results:

=SUM(NETWORKDAYS.INTL({"6-jun";"6-jun";"9-jun"},{"6-jun";"9-jun";"9-jun"},{1,"1111101"})*
({"10:00","10:00";"17:30","13:30";"17:30","13:30"}-{"9:30";"9:30";"16:00"})*24*{-1;1;-1})

With cell references containing the date times:

=SUMPRODUCT(NETWORKDAYS.INTL(IF({1;1;0},B2,C2),IF({1;0;0},B2,C2),{1,"1111101"})*
(IF({0;1;1},{"17:30","13:30"},MOD(B2,1))-IF({1;1;0},"9:30",MOD(C2,1)))*24*{-1;1;-1})