Excel Formula to calculate the time difference including Weekends

Ebraham

Board Regular
Joined
Mar 21, 2015
Messages
215
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 ?
 

Some videos you may like

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
Joined
Oct 24, 2004
Messages
752
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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")

also, more info here:-
 

Watch MrExcel Video

Forum statistics

Threads
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.
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