# Blog

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

January 21. 2021

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:

- This blog post by Carlos Figueira provides a detailed explanation of this topic:

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`

,

`, Days) / 7, 0) * 5 +`

`endDate`

Mod(5 + Weekday(

`) - Weekday(`

`endDate`

`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)?

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`

,

`, Days) / 7, 0) * 5 +`

`endDate`

Mod(5 + Weekday(

`) - Weekday(`

`endDate`

`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(`/5, 0)*2 +`

`daysToAdd`

Switch(Weekday(`startDate`

, StartOfWeek.Monday),

5,If(Mod(`, 5)>0,2,0),`

`daysToAdd`

4,If(Mod(`, 5)>1,2,0),`

`daysToAdd`

3,If(Mod(`, 5)>2,2,0),`

`daysToAdd`

2,If(Mod(`, 5)>3,2,0),`

`daysToAdd`

1,If(Mod(`, 5)>4,2,0)`

`daysToAdd`

)

)

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.

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.## 4. Adding X business days to a date (excluding public holidays)

`With({ startDate:DateValue("2021-01-18"),`

`daysToAdd: 7`

},

DateAdd(`startDate`

,`) +`

`daysToAdd`

RoundDown(`/5, 0)*2 +`

`daysToAdd`

Switch(Weekday(`startDate`

, StartOfWeek.Monday),

5,If(Mod(`, 5)>0,2,0),`

`daysToAdd`

4,If(Mod(`, 5)>1,2,0),`

`daysToAdd`

3,If(Mod(`, 5)>2,2,0),`

`daysToAdd`

2,If(Mod(`, 5)>3,2,0),`

`daysToAdd`

1,If(Mod(`, 5)>4,2,0)`

`daysToAdd`

) +`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

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

- Categories:
- formula
- dates times

Previous

__Related posts__

- Formula - How to add a button that converts degrees Centigrade to Fahrenheit and vice versa
- Formula - How to convert a single delimited string to rows and columns
- Data - How to group data in a gallery and calculate sums
- Formula - How to calculate compound interest
- Utilities - The best way to peform OCR on images of Power Apps Formulas
- Example - How to use a drop down control to convert currencies
- Formula - How to parse JSON in Power Apps- 4 examples
- Data - How to get a row by ordinal number
- Formula - What to do when the If statement doesn't work?
- Formula - Boolean And / Or operators - What is the order of precedence?
- Controls - How to set the data source of a Combo Box to a comma separated string
- Numbers - 10 examples of how to round numbers
- Formula - Difference between round, square, and curly brackets
- Top 3 highlights of upcoming enhancements to the Power Apps language (Power FX)
- Email - Sending email attachments with the Office 365 Outlook connector
- Formula - What to try when numbers don't format correctly
- Controls - How to convert HTML to Text
- Formulas - how to return all days between two dates
- Formula - How to create comma separated (CSV) list of items
- Formula - How to use the IF and Switch functions - 3 common examples
- Location - Finding the closest location and and sorting records by distance, based on the current location of the user