Article 32 – Calculate Working Hours between Two Ranges and Exclude Weekends and Holidays (SLA Calculation)

Suppose you have two time stamps say A1: 23-Dec-2015 09:15 AM and B1: 29-Dec-2015 02:30 PM and say your working hours are between 09:00 AM to 05:00 PM. You have been tasked to calculate the working hours between these two dates and you need to exclude weekends (here – 26-Dec-15 and 27-Dec-15) and any holiday (here – 25-Dec-15). Below is the detail calculation for this –

< Download the workbook related to this article from Article 32 – Working Hours Excluding Weekends and Holidays >

Similarly, if A1:12/23/2015  6:15 AM and A2: 12/30/2015  8:30 PM, then below will be the calculation –

Now, if we have a Excel sheet full of Start Date / Time and End Date / Time (like in case of incidents), then how to calculate this by a single formula. This is a general scenario in SLA based environment.

Let's assume that we have a layout like below where Start and End times are in H2 and H3 and Holiday list is in I2 onwards.

Then formula for calculating Elapsed Working hours would be

=(NETWORKDAYS(A2,B2,$I$2:$I$15)-1)*($H$3-$H$2)+IF(NETWORKDAYS(B2,B2),
MEDIAN(MOD(B2,1),$H$3,$H$2),$H$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),$H$3,$H$2)

Above formula assumes that weekends are Saturdays and Sundays.

What if, weekends are different. For example, Gulf Countries have Friday and Saturdays, in general, as weekends. Also many countries / companies may have only single day a weekend. Then above formula will not be able to handle these scenario as weekends are fixed in NETWORKDAYS which are Saturday and Sunday.

Starting Excel 2010, NETWORKDAYS.INTL has been introduced which controls what are defined as weekends. The syntax for NETWORKDAYS.INTL is NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]). [weekend] parameter defines what days are considered weekends. Below are the values for [weekend] to be used –

Now, the formula using NETWORKDAYS.INTL will be following where 1 in red is weekend parameter.

=(NETWORKDAYS.INTL(A3,B3,1,$I$2:$I$15)-1)*($H$3-$H$2)+IF(NETWORKDAYS.INTL(B3,B3,1),
MEDIAN(MOD(B3,1),$H$3,$H$2),$H$3)-MEDIAN(NETWORKDAYS.INTL(A3,A3,1)*MOD(A3,1),$H$3,$H$2)