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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
748
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,123,265
Messages
5,600,605
Members
414,393
Latest member
Vignesh Mechz

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