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
Max_Last_Updated_On, Min_First_Assigned_On2014-01-05, 2014-01-092014-02-04, 2014-02-042014-03-03, 2014-03-112014-04-02, 2014-04-072014-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.
Max_Last_Updated_On, Min_First_Assigned_On, Series, Weekday2014-01-05, 2014-01-09, 2014-01-05, 02014-01-05, 2014-01-09, 2014-01-06, 12014-01-05, 2014-01-09, 2014-01-07, 12014-01-05, 2014-01-09, 2014-01-08, 12014-01-05, 2014-01-09, 2014-01-09, 12014-02-04, 2014-02-04, 2014-02-04, 12014-03-03, 2014-03-11, 2014-03-03, 12014-03-03, 2014-03-11, 2014-03-04, 12014-03-03, 2014-03-11, 2014-03-05, 12014-03-03, 2014-03-11, 2014-03-06, 12014-03-03, 2014-03-11, 2014-03-07, 12014-03-03, 2014-03-11, 2014-03-08, 02014-03-03, 2014-03-11, 2014-03-09, 02014-03-03, 2014-03-11, 2014-03-10, 12014-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.
Max_Last_Updated_On, Total_Weekday2014-01-05, 42014-02-04, 12014-03-03, 72014-04-02, 42014-05-01, 2 |
Comments
0 comments
Please sign in to leave a comment.