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

khaledabdelazim

New Member
Joined
Dec 8, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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:

Some videos you may like

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
Joined
Feb 15, 2002
Messages
4,629
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABC
1
21-Dec-2031-Dec-2027
3
5a
Cell Formulas
RangeFormula
C2C2=NETWORKDAYS.INTL(A2,B2,16,K2:K8)
 

khaledabdelazim

New Member
Joined
Dec 8, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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
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
Joined
Feb 15, 2002
Messages
4,629
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top