Excluding weekends and holidays in date differences in PowerApps

Calendars are often the glue that keep our busy lives in tact. But what if your organization only needs to consider the business days in your application? Inspired by the multiple community discussions surrounding this topic, Carlos Figueira is back to drop some more PowerApps knowledge on his detailed solution for how you can calculate the number of business days between any two given dates.

Check out his breakdown below!

The DateDiff function allows an app to calculate the difference between two dates – a simple calendar operation. However, in many business applications, one needs to remove weekends when getting the difference – the app needs to know how many business days are between two given dates. There are a few threads in the forums with the same issue, so I decided to write a detailed post with a solution for this problem.

Excluding weekends

The first part of the problem to calculate the difference in business days is to exclude the weekends in the calculation. To start with that, we first need to calculate the number of whole weeks between the two dates, which we can get by taking the whole part of the division (using the RoundDown function) of the “raw” date difference by 7:

RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0)

And then the difference of the days not in the whole weeks. For that we need to use the Weekday function. In the function below, if the start and end dates are between Monday and Friday (weekdays from 2 to 6), their difference will be in the -4 (start on Friday, end on Monday) to 4 (start on Monday, end on Friday) range; to get the number of days we use a modular operation to convert between that range to 0 to 4 which gets us the number of days in the “partial week” that we didn’t count in the whole weeks:

Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5)

Putting it all together, we have the number of days, excluding weekends, between two days as the number of weeks times 5 (days per week), plus the number of days in the partial week:

RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 5 +
Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5)

Notice that the partial week calculation given above will not work if the start or end dates fall on weekends; in this case we’d need to decide what to do (either use the previous Friday or the following Monday) in the calculations – and that will depend on the application.

Dealing with holidays

Holidays are not something for which we can find a universal formula – each country (or region within a country) has its own list of holidays. To account for that then, we need to have the information of holidays stored somewhere (e.g., in an Excel file, in a SQL database table, etc.) so that we can use that in our logic. In this example, I’ll use an Excel table with the US holidays:

Once that table is imported into the app (named Holidays2017 in my case), then we can use the CountIf function to determine how many of those holidays fall within our date range:

CountIf(Holidays2017, Date >= StartDate.SelectedDate, Date <= EndDate.SelectedDate)

And finally we can put everything together to get the difference in business days between two dates:

RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 5 +
Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5) -
CountIf(Holidays2017, Date >= StartDate.SelectedDate, Date <= EndDate.SelectedDate)