PowerApps - Business Days Calculation(Exclude weekends and Holidays)
Background
Recently I had a client requirement to calculate Business days in PowerApps. I checked the available formulas in PowerApps and surprised to know that there is no Out of the box formula to calculate business days. In Microsoft Excel, there is a formula NETWORKDAYS(start_date, end_date, [holidays]) , it takes start_date and end_date to calculate the business days within that date range. Also, there is a third parameter [holidays], you can select the excel range with list of holidays and this formula will substract these days from the date range.
I came across a
blog post in PowerApps community site, however these formulas has a limitation of weekend selection. If your start date or end date is on a weekend this formula fails to calculate the business days correctly.
How to exclude weekends
You can use below formula to calculate business days,
1 + ((DateDiff(dtStartDate.SelectedDate,dtEndDate.SelectedDate,Days))
* 5 - ((Weekday(dtStartDate.SelectedDate) -
Weekday(dtEndDate.SelectedDate))*2)) / 7 -
Switch(Weekday(dtEndDate.SelectedDate),7,1,0) -
Switch(Weekday(dtStartDate.SelectedDate),1,1,0)
Here dtStartDate /dtEndDate are calendar controls.
In this formula, additional one day is subtracted from the calculated business days if the selected Startdate is on Sunday or selected Enddate in on Saturday.
Also note that 1+ is added to the beginning of the formula. If you use 1+ , formula assumes start of first day until end of last day . In certain business scenarios, you might need to exclude current day, in that case exclude 1+ which means end of first day until end of last day.
How to exclude holidays
There is no standard formula to exclude holidays, as every country or every region within a country has different holidays. E.g. South India has few holidays different than North India within same country. I have used a collection to create the list of Holidays. You can store it in an MS Excel or a SQL database as per your preference. Here are the steps to exclude holidays,
- Assign the holiday list to a Datatable
- Change the Visible property of Datatable to false
- Use below formula
CountIf(HolidayCalendar, DateValue(HolidayDate)
>= dtStartDate.SelectedDate, DateValue(HolidayDate)
<= dtEndDate.SelectedDate)
Here HolidayCalendar is the datasource having list of holidays, dtStartDate & dtEnDate are date controls.
Countif function calculates the number of days falling between this selected date range.
Summary
PowerApps doesn’t have OOB function to calculate business days excluding the holidays. This article and steps showcased in the video will help you to calculate the business days similar to NetworkDays function in Microsoft Excel.