cancel
Showing results for
Did you mean:

Calculating Business Days Difference Between Two Dates

Community Team Member

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.

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

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

The external resources needed are:

2. 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.

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:

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:

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

Step 3 - Create the widget

For the example above, we will select the ‘Ship Country’ as category and add the custom field we calculated as value in Average mode.

Date list 2012-2020.csv

Ecube

US Bank Holidays 2012-2020.csv

Version history
Last update:
‎10-17-2021 06:51 AM
Updated by:
Contributors
Community Toolbox

Developers Group:

Product Feedback Forum: