How to Calculate Number of Business Days Between Two Dates

Goal

With Date and Time Functions it is possible to calculate the difference in calendar days. Now, learn how to calculate difference in business days.

Learn

Step-by-Step Guide

In this use case it is necessary to exclude Saturdays and Sundays. 

To give a better picture of the use case, a small sample data set

Worksheet CalculateBusinessDays
Max_Last_Updated_On, Min_First_Assigned_On
2014-01-05, 2014-01-09
2014-02-04, 2014-02-04
2014-03-03, 2014-03-11
2014-04-02, 2014-04-07
2014-05-01, 2014-05-03

To calculate the difference in business days it is necessary to find out to which day of the week a particular day belongs to. The function WEEKDAY will do this for a DATE value. Since there is a start and a end date which vary, there is the need to do this for every day within the range. EXPAND_DATE_RANGE will help for that.

added a new worksheet "TimeSeries" and within the columns #Series and #Weekday. For the columns I used the formulas

=GROUPBY(#CalculateBusinessDays!Max_Last_Updated_On)
=#CalculateBusinessDays!Min_First_Assigned_On
=EXPAND_DATE_RANGE(#Max_Last_Updated_On;#Min_First_Assigned_On)
=IF(WEEKDAY(#Series)<6;1;0)

whereby the column #Weekday will give the possibility to count or sum up days.

Worksheet TimeSeries
Max_Last_Updated_On, Min_First_Assigned_On, Series, Weekday
2014-01-05, 2014-01-09, 2014-01-05, 0
2014-01-05, 2014-01-09, 2014-01-06, 1
2014-01-05, 2014-01-09, 2014-01-07, 1
2014-01-05, 2014-01-09, 2014-01-08, 1
2014-01-05, 2014-01-09, 2014-01-09, 1
2014-02-04, 2014-02-04, 2014-02-04, 1
2014-03-03, 2014-03-11, 2014-03-03, 1
2014-03-03, 2014-03-11, 2014-03-04, 1
2014-03-03, 2014-03-11, 2014-03-05, 1
2014-03-03, 2014-03-11, 2014-03-06, 1
2014-03-03, 2014-03-11, 2014-03-07, 1
2014-03-03, 2014-03-11, 2014-03-08, 0
2014-03-03, 2014-03-11, 2014-03-09, 0
2014-03-03, 2014-03-11, 2014-03-10, 1
2014-03-03, 2014-03-11, 2014-03-11, 1
...

Now the report can be created on an other sheet called "BusinessDays"

=GROUPBY(#TimeSeries!Max_Last_Updated_On)
=GROUPSUM(#TimeSeries!Weekday)

wich will give the following result.

Worksheet BusinessDays
Max_Last_Updated_On, Total_Weekday
2014-01-05, 4
2014-02-04, 1
2014-03-03, 7
2014-04-02, 4
2014-05-01, 2