How to find business days excluding holidays between two dates?¶
Recently, while working with one of our clients we came across an interesting data problem. Take a look at the
In the dataset above, we need to find the payment delays between the Contractual settlement date and the Actual settlement date. But adjusted for weekends and holidays.
So if the contractual payment date was on 21st April 2021 and the settlement happened on 25th April 2021, with two bank holidays in between, the delay would be (25th-21st)-2 = 2 days.
Similarly, if the contractual date was 18th April 2021, the settlement date was 21st April 2021, and the weekends (Saturday and Sunday) fell in between, the delay would be (21st-18th)-2 = 1 day.
So here’s the challenge. We need to take into account different holiday calendars based on the currencies in which the settlements take place.
That is, if the payments are in US dollars, we need to adjust the delays as per the US bank holidays. Similarly, for payments in Euro or Pounds, we need to adjust the delays as per the UK bank holidays and so on.
It would have been a simple problem if we just had to find the difference between the Contractual and Actual settlement dates. We could have achieved this with Mammoth’s date difference function in seconds.
Instead, we need to incorporate different holiday calendars for payments in different currencies. This is what makes this problem edgy.
Here’s how to logically go about the problem:
First, create a calendar dataset.
Now, get the holiday calendars of each country in a specified format and upload that to Mammoth.
Join the holidays with the calendar dataset and mark the holidays and weekends in the year.
After this, if somehow we can figure out the total number of business days till a certain date, then we can easily find effective business days between any two dates just by subtracting the two.
Let’s see how we do this in Mammoth:
Step 1. Take out the calendar¶
Create a separate calendar dataset with columns like Date and Day.
Step 2. Bring in the holidays¶
Next, bring in the holidays for different countries as a Holiday column in the Calendar dataset above with a Join rule. Tie different holiday datasets to country/currency codes to make the process simpler.
This is what we’ll get:
Step 3. Find business days¶
Next, use the Label and Insert function with the following conditions to mark the business days and the holidays:
For every business day (i.e. when the Holiday column is empty, and the days are not Saturday and Sunday), the value should be 1.
For every holiday and/or weekend, (i.e. when the Holiday column is NOT empty, and/or days are Saturday and Sunday), the value should be 0.
Enter the results into a new column named Business days.
It’ll mark the business days as 1:
With this, we know all the business days in the year. We can use the SUM function to calculate the total business days in the year.
Step 4. Calculate the sum of business days¶
Now, use the window function to calculate the running sum of the column Business days. This will give us the total business days till a certain date. It will come in handy in finding business days between any two dates.
For example, if the total business days till say 4/04/2022 is 50 and the total business days till 20/04/2022 is 60, the effective business days between these two dates will be the difference between these two values. That is, 60-50 = 10, there are 10 business days between dates 4/04/2022 and 20/04/2022.
It’ll look something like this:
Step 5. Bring business days into the original DS¶
To find the effective business days between the Contractual and Actual dates, we will apply two Joins:
One Join to bring the total business days till the Contractual settlement date, the Contractual date being the key column
Another join to bring the total business days till the Actual settlement date, the Actual date being the key column
With this, we have two columns with total business days corresponding to Contractual and Actual settlement dates:
We can easily calculate the difference between these columns with a math rule to find the effective business days between these date ranges.
Step 6. Calculate difference¶
As we said, we will now find the difference between the joined business days column from Step 6. This will give us the effective business days between these two dates.
The effective business days here also denote the delay in settlements, excluding the holidays and weekends: