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

#### khaledabdelazim

##### New Member
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

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)))``

I wish someone could help here

Last edited by a moderator:

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Dave Patton

##### Well-known Member
Try using the function shown below instead of Networkdays.
N.B. A range for holiday dates is optional.

T202012a.xlsm
ABC
1
21-Dec-2031-Dec-2027
3
5a
Cell Formulas
RangeFormula
C2C2=NETWORKDAYS.INTL(A2,B2,16,K2:K8)

#### khaledabdelazim

##### New Member
Try using the function shown below instead of Networkdays.
N.B. A range for holiday dates is optional.

T202012a.xlsm
ABC
1
21-Dec-2031-Dec-2027
3
5a
Cell Formulas
RangeFormula
C2C2=NETWORKDAYS.INTL(A2,B2,16,K2:K8)
[/RANGE
Cell Formulas
RangeFormula
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

##### Well-known Member
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?

Replies
26
Views
1K
Replies
1
Views
165
Replies
7
Views
388
Replies
2
Views
58
Replies
4
Views
385

1,127,831
Messages
5,627,149
Members
416,223
Latest member
RichardHell

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back