How to calculate time difference between two dates in excel excluding Friday Not Sat and Sunday?

[khaledabdelazim]

Hi All,

I know this question may have been answered many times, I am trying to calculate the time taken for incoming tickets till resolution, excluding days off and off working hours

I have tested the following formula and it satisfies this need very well, yet I don't know how to adapt it to Friday as a day off instead of the common Sat,Sun

=IF(OR(C4="",D4=""),"",(NETWORKDAYS(C4,D4,$H$4:$H$12)-1)*($G$5-$G$4)+IF(OR(ISNUMBER(MATCH(INT(D4),$H$4:$H$12,0)),WEEKDAY(D4,2)>5),$G$5,MEDIAN(MOD(D4,1),$G$5,$G$4))-IF(OR(ISNUMBER(MATCH(INT(C4),$H$4:$H$12,0)),WEEKDAY(C4,2)>5),$G$4,MEDIAN(MOD(C4,1),$G$5,$G$4)))

I wish someone could help here

 

[Dave Patton]

Try using the function shown below instead of Networkdays.
See Excel's help for more information.
N.B. A range for holiday dates is optional.

T202012a.xlsm
	A	B	C
1
2	1-Dec-20	31-Dec-20	27
3
5a

Cell Formulas
Range	Formula
C2	C2	=NETWORKDAYS.INTL(A2,B2,16,K2:K8)

 

[khaledabdelazim]

Try using the function shown below instead of Networkdays.
See Excel's help for more information.
N.B. A range for holiday dates is optional.

T202012a.xlsm
	A	B	C
1
2	1-Dec-20	31-Dec-20	27
3
5a

Cell Formulas
Range	Formula
C2	C2	=NETWORKDAYS.INTL(A2,B2,16,K2:K8)

[/RANGE

Cell Formulas
Range	Formula

Thank you Dave for your reply, but this formula doesn't take the working hours into consideration My team operates from 9 AM till 7 PM , tickets incoming at 8 PM should start counting at 9 AM next day Regards Khaled

 

[Dave Patton]

You stated "
I have tested the following formula and it satisfies this need very well, yet I don't know how to adapt it to Friday as a day off instead of the common Sat,Sun

Excel Formula:
=IF(OR(C4="",D4=""),"",(NETWORKDAYS(C4,D4,$H$4:$H$12)-1)*($G$5-$G$4)+IF(OR(ISNUMBER(MATCH(INT(D4),$H$4:$H$12,0)),WEEKDAY(D4,2)>5),$G$5,MEDIAN(MOD(D4,1),$G$5,$G$4))-IF(OR(ISNUMBER(MATCH(INT(C4),$H$4:$H$12,0)),WEEKDAY(C4,2)>5),$G$4,MEDIAN(MOD(C4,1),$G$5,$G$4)))

Did you try NETWORKDAYS.INTL instead of Networkdays in that formula?