Calculating Business Days Difference Between Two Dates

Community_Admin · ‎10-17-2021

Introduction

This article demonstrates how to avoid counting weekends and holidays when calculating the difference between two dates.

Example - Average Shipping Time in Business Days

Steps

In order to ‘subtract’ two dates and avoid counting the weekends and holidays , we will \ calculate a “business value” for each date within a certain date range.

Step 1

Option 1 - Using Pre-calculated Business Days CSV file

The business days in the csv file were calculated based on the Bank Holiday for the US. Please verify the dates before using the file.

If your use case requires different business days, please create a CSV file with the relevant holidays and calculations. You can also refer to option 2 (using the ElastiCube Manager).

The CSV File holds a table with 3 columns:

A continuous date list for the entire date range
Business Day flag - 0 for holidays and weekends, 1 for business days
Business Value - a running sum of the ‘Business Days’ flag

Download the 'US Dates with Business Values.csv' file and import the table to your ElastiCube, build it and skip to "STEP 2".

Please note that the provided table includes the Holiday Bank between 2012-2020. The lists should be revised and updated in case historical dates are required and maintained when approaching 2020

Option 2 (Advanced) - Calculating Business Days in the ElastiCube Manager

The external resources needed are:

Relevant “Bank Holiday” which can be easily downloaded online
A continuous date list according to the Bank Holiday range, the list can be easily prepared using Excel.

In the above example I used the US Bank holidays which can be found at https://gist.github.com/shivaas/4758439

Attached are the US Bank Holiday and Date List CSVs that have been used for the example.

Download and Import the relevant CSVs to the ElastiCube and build the cube.

Please note that the provided lists and Holiday Bank includes dates between 2012-2020. The lists should be revised and updated in case historical dates are required and maintained when approaching 2020.

Create the business-day flag

For each date within the date list, we will attach ‘1’ for business days and ‘0’ for weekends or holidays. For that, we will use a custom column with the following syntax:

CASE

WHEN lookup([US Bank Holidays 2012-2020.csv],[Bank Holiday],Date,Date) = 'TRUE' OR dayofweek([Date])>5 THEN 0

ELSE 1

END

Please perform a Schema Changes build for applying changes for upcoming steps.

Calculate the ‘Business Value’ for each date

In the previous step, we added the business day flag to each date. In this step we will create a custom table that will incrementally sum the business day flags for each date resulting in a ‘Business Value’. This value will represent the number of passed business-days relative to the first day in that range.

For that we will create a custom SQL table with the following syntax:

SELECT a.Date, sum(b.BusinessDay) [Business Value]

FROM [Date list 2012-2020.csv] a JOIN [Date list 2012-2020.csv] b

ON a.Date >= b.Date

GROUP BY a.Date

Set the tables we added to “Invisible” and build schema changes again.

Step 2 - Lookup the 'Business Value'

In the Example above, we calculated the average business days it takes to ship for each country. For that, we need the business days difference between the ‘ShippedDate’ and ‘OrderDate’. We will lookup the ‘Business Value’ of each date from the table we created\imported and the difference will be the ‘business days difference’ between those two dates.

For that we will use the following syntax:

Lookup([Dates Bsiness Values],[Business Value],ShippedDateFixed,Date) - Lookup([Dates Bsiness Values],[Business Value],OrderDateFixed,Date)

Note - If the dates are not stored in the same table, each ‘Business Value’ should be looked up to each table separately.