# Excel Formula to calculate the time difference including Weekends

#### Ebraham

##### Board Regular
Hi,

I have got a formula which calculates the working hours between the two dates excluding the weekends and holidays. Office working hours are 9.30 to 6.30. Below is the formula.

E2 is the Start Date & Time and M2 is the End.

=(NETWORKDAYS.INTL(E2,M2,1,\$AI\$2:\$AI\$4)-1)*("18:30"-"9:30")+IF(NETWORKDAYS.INTL(M2,M2,1,\$AI\$2:\$AI\$4),MEDIAN(MOD(M2,1),"9:30","18:30"),"18:30")-MEDIAN(NETWORKDAYS.INTL(E2,E2,1,\$AI\$2:\$AI\$4)*MOD(E2,1),"9:30","18:30")

What do i need to fine tune if i want to include the weekends and holidays time as well ?

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Taul

##### Well-known Member
Hi,
I have retained the original format of the formula but removed the holidays and added the Networksdays mask of 7 digits, one for each day of the week.
0 = working day, 1 = non-working day (weekend). It starts on a Monday, so a string of "0000011" would imply a working week with sat & sun set as the weekend. So you have the option to change it in the future.
You could also retain the Holiday range in the formula but simply leave the holiday range blank if you wish.
Leaving all seven digits as zero, means that all days of the week are considered.

Code:
``=(NETWORKDAYS.INTL(E2,M2,"0000000")-1)*("18:30"-"9:30")+IF(NETWORKDAYS.INTL(M2,M2,"0000000"),MEDIAN(MOD(M2,1),"9:30","18:30"),"18:30")-MEDIAN(NETWORKDAYS.INTL(E2,E2,"0000000")*MOD(E2,1),"9:30","18:30")``

Replies
6
Views
130
Replies
1
Views
138
Replies
26
Views
1K
Replies
0
Views
416
Replies
7
Views
366

1,127,049
Messages
5,622,410
Members
415,895
Latest member
Akhilesh28

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