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 following dataset:

sample dataset

Fig. 206 Sample dataset

Our Goal

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.

The Challenge

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.

The Solution

Here’s how to logically go about the problem:

  1. First, create a calendar dataset.

  2. Now, get the holiday calendars of each country in a specified format and upload that to Mammoth.

  3. Join the holidays with the calendar dataset and mark the holidays and weekends in the year.

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

label and insert rule

Fig. 207 Calendar dataset

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.

joining holidays

Fig. 208 Joining the Holidays’ dataset with the Calendar dataset

This is what we’ll get:

label and insert rule

Fig. 209 Joined with US holiday dataset

Step 3. Find business days

Next, use the Label and Insert function with the following conditions to mark the business days and the holidays:

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

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

label and insert rule

Fig. 210 Applying Label and Insert rule

It’ll mark the business days as 1:

label and insert rule

Fig. 211 Business days counted 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.

sample dataset

Fig. 212 Finding the running total of business days using the Window Function

It’ll look something like this:

sample dataset

Fig. 213 Running sum of business days

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:

  1. One Join to bring the total business days till the Contractual settlement date, the Contractual date being the key column

contractual join

Fig. 214 Joined business days with the Contractual date

  1. Another join to bring the total business days till the Actual settlement date, the Actual date being the key column

joined business days with the original DS

Fig. 215 Joined business days with the Actual date

With this, we have two columns with total business days corresponding to Contractual and Actual settlement dates:

joined business days with the original DS

Fig. 216 Joined business days with contractual and actual dates separately

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.

effective business days

Fig. 217 Finding effective business days

The effective business days here also denote the delay in settlements, excluding the holidays and weekends:

Result dataset

Fig. 218 Final result