Blog

Formulas - How to cope with weekends and public holidays in date calculations

Performing date calculations that take working days and public holidays into account can be complicated. This post summarises and verifies the formula to calculate the number of working days between two dates, and the formula to add a given number of working days to a start date.

A common requirement is to perform date calculations based on values that exclude weekends, and optionally public holidays. Unfortunately, there are no built-in functions to perform these type of date calculations and therefore, it's necessary to write custom formula.

For these types of calculations, the two best resources that I usually refer to are these:

In this post, I'll summarise the formula's from the above resources to highlight the three most common scenarios that I see. These are -  how to calculate the number of business days between two dates, how to calculate the number of business days between two dates excluding public holidays, and how to add X number of working days to a start date.

1. How many business days are there between 2 dates?

Here's the syntax to calculate the number of business days between two dates (excluding weekends)

The formula beneath takes the start date 18 January 2021, and the end date 29 January 2021. There are 11 full days between these two values - the equivalent of 9 working days.

With({ startDate:DateValue("2021-01-18"),
       endDate:DateValue("2021-01-29")
      },
      RoundDown(DateDiff(startDate, endDate, Days) / 7, 0) * 5 +
      Mod(5 + Weekday(endDate) - Weekday(startDate), 5)
)
This formula works by calculating the number of full weeks between the start and end date values and then multiplying this value by 5 (eg, the number of days in a working week). Next, it adds the number of partial days that fall outside of the full weeks.

As the screenshot beneath shows, this formula returns the expected number of working days between these two dates (ie, 9 days).

An important caveat about this technique is that the calculation will not work if the start or end dates fall on weekends. In this case, we would need to adapt the formula so that it uses the previous Friday, or the following Monday.

2. How many business days are there between 2 dates (excluding public holidays)? 

A common requirement is to adapt this type of date calculation exclude public holidays. To accomplish this, we first need to define and store our public holidays in an accessible location. This could be in a data source, or a collection.

To demonstrate, the formula beneath defines a collection of public holidays. I included two variations of this collection - one with US holidays for 2021, and the other with UK holidays for 2021.

We can add this ClearCollect logic to the OnStart property of our app.

//US Holidays 2021
ClearCollect(colHolidays,
             Split("2021-01-01, 2021-01-18, 2021-02-15, 2021-05-31,
                   2021-07-14, 2021-09-06, 2021-10-11, 2021-11-11,
                   2021-11-25,2021-12-25",
                   ","
             )
)

//UK Holidays 2021
ClearCollect(colHolidays,
             Split("2021-01-01, 2021-04-02, 2021-04-05, 2021-05-03,
                   2021-05-31,2021-08-30, 2021-12-27, 2021-12-28",
                   ","
             )
)

The column name for this collection is "Result", and the date values in this collection are of data type string.

To work out the difference between two date values, we base our logic on our previous formula. The additional step we need to carry out is to also subtract the days that match those in our colHolidays collection.

The formula beneath takes the start date 01 January 2021, and the end date 22 January 2021. There are 21 full days between these two values - the equivalent of 16 working days.There are two public US holidays (2021-01-01, 2021-01-18), so our expected result is 14 days.

With({ startDate:DateValue("2021-01-01"),
       endDate:DateValue("2021-01-22")
      },
      RoundDown(DateDiff(startDate, endDate, Days) / 7, 0) * 5 +
      Mod(5 + Weekday(endDate) - Weekday(startDate), 5) - 
      CountIf(colHolidays, DateValue(Result) >= startDate, DateValue(Result) <= startDate)
)

As the screenshot beneath shows, this formula returns our expected number of days (14 days).

3. Adding X business days to a date

To add a given number of business days to a start date, we can use the formula beneath which is an adaptation of formula from Siena's post. Here, we can specify start date and the number of days to add inside the "With" block. We can adjust these values to suit our needs.

With({ startDate:DateValue("2021-01-18"),
       daysToAdd: 9
      },
      DateAdd(startDate, daysToAdd) + 
      RoundDown(daysToAdd/5, 0)*2 +
      Switch(Weekday(startDate, StartOfWeek.Monday),
             5,If(Mod(daysToAdd, 5)>0,2,0),
             4,If(Mod(daysToAdd, 5)>1,2,0),
             3,If(Mod(daysToAdd, 5)>2,2,0),
             2,If(Mod(daysToAdd, 5)>3,2,0),
             1,If(Mod(daysToAdd, 5)>4,2,0)
      )
)
Taking the values from our earlier example, 18 January 2021 plus 9 working days should result in an end date of 29 January 2020. As the screenshot beneath shows, this formula returns our expected date.

4. Adding X business days to a date (excluding public holidays)

To take public holidays into account, we can reuse our formula from above, and add the additional days that match values in our colHolidays collection.

With({ startDate:DateValue("2021-01-18"),
       daysToAdd: 7
      },
      DateAdd(startDate, daysToAdd) + 
      RoundDown(daysToAdd/5, 0)*2 +
      Switch(Weekday(startDate, StartOfWeek.Monday),
             5,If(Mod(daysToAdd, 5)>0,2,0),
             4,If(Mod(daysToAdd, 5)>1,2,0),
             3,If(Mod(daysToAdd, 5)>2,2,0),
             2,If(Mod(daysToAdd, 5)>3,2,0),
             1,If(Mod(daysToAdd, 5)>4,2,0)
      ) +
      CountIf(colHolidays, DateValue(Result) >= startDate, DateValue(Result) <= startDate)
)

Taking the values from our earlier example, 01 January 2021 plus 14 working days, plus two public US holidays (2021-01-01, 2021-01-18) should result in an end date of 22 January 2020.

As the screenshot beneath shows, this formula returns our expected date.

How to additionally verify our results

With these type of calucation, it's always a good idea to verify that they produce the correct results. We can carry out quick, additional sanity checks by using an online calculator, such as the one beneath.



My feature idea to improve date calculations in Power Apps

It's unfortunate that we must resort to these types of complex formula to perform date calculations on working days and public holidays.

Microsoft Excel provides a built-in function called WORKDAY to more easily carry out this task. The usage instructions are here.
https://support.microsoft.com/en-us/office/workday-function-f764a5b7-05fc-4494-9486-60d494efbf33

WORKDAY(start_date, days, [holidays])

The WORKDAY function syntax has the following arguments:


  • Start_date - A date that represents the start date.
  • Days - The number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.
  • Holidays - An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates
I propose that Power Apps should include an implementation of this function, which would make it much easier to perform these types of calculation.

If you agree, you can vote for this feature idea here:

Conclusion

To perform date calculations that take working days and public holidays into account, we need to write custom formula. This post summarises and verifies the formula to calculate the number of working days between two dates, and the formula to add working days to a start date.
Previous
Next
Related posts