Power Apps Calculate Business Days Excluding Weekends & Holidays

Power Apps Calculate Business Days Excluding Weekends & Holidays

Calculating the number of business days between two days is a common feature of many apps. In Power Apps can do this by generating a list of dates and filtering out the weekends and holidays. There is no NETWORKDAYS function like Microsoft Excel but with a few easy steps we can create the same functionality ourselves. This Power Apps article will show you how to calculate the number of business days.


Table Of Contents:
 •  Introduction: The Vacation Requests AppSetup A SharePoint List For Vacation RequestsCreate A New Canvas App In Power Apps StudioInsert An Edit Form To Capture Vacation Start & End DatesCalculate The Number Of Business Days Excluding Weekends In Power Apps  Setup A SharePoint List For Holiday CalendarsExclude Holidays From The Business Days Calculation In Power AppsValidate Start Date & End Date Are Not On Weekends Or HolidaysSubmit The Vacation Request Form DataTest The Completed Vacation Request Form




Introduction: The Vacation Requests App

The Vacation Requests App is used by employees at a financial services firm to ask for paid-time off. Employees input the start date and end date of their vacation and the app calculates the number of vacation days excluding weekends and company holidays.




Setup A SharePoint List For Vacation Requests

Create a new SharePoint list called Vacation Requests with the following columns:

  • RequestedBy (single-line text)
  • Start Date (date only)
  • End Date (date only)
  • Number Of Days (number)



The Vacation Requests SharePoint list should look like this once a few requests are submitted. We do not need to load it with data in advance of building the app.





Create A New Canvas App In Power Apps Studio

Open Power Apps Studio and create a new app from blank. Then insert a button onto the screen with a white fill and no text onto the center of the screen. We will use the button as a card to hold a title, a form and a submit button.



Use these values in their respective properties to style the button as shown in the screenshot above. Setting the DisplayMode property to View makes it so the button cannot be clicked.

DisplayMode: DisplayMode.View
Fill: White
Height: 400
Width: 500
X: (App.Width-Self.Width)/2
Y: (App.Height-Self.Height)/2



Also, use this code in the Fill property of the screen to change it to a light gray color.

RGBA(237, 237, 237, 1)



Next, our card needs a title. Create a new label and position it at the top of the card.



Fill-in the label with these properties to achieve the same look and feel as the screenshot above.

Font: 'Segoe UI'.Font
FontWeight: FontWeight.Semibold
PaddingLeft: 30
Size: 20




Insert An Edit Form To Capture Vacation Start & End Dates

Employees must fill-in a vacation request form to see how many business days-off are needed. We need to connect the Vacation Requests SharePoint list to our app to build the form. Go to the data menu and select the Vacation Requests list using the SharePoint connector.



Insert a new Edit Form onto the screen and position it on top of the card. Choose Vacation Requests as the datasource.



Write this code in the DefaultMode property of the Vacation Request form.

FormMode.New



Position the form’s input fields in this order from top-to-bottom: Requested By, Start Date, End Date, Vacation Days.



The Vacation Days will be automatically calculated by the app. We don’t want users typing in their own values.



Write this code in the DisplayMode property of the Number Of Days form card to make it view-only.

DisplayMode.View




Calculate The Number Of Business Days Excluding Weekends In Power Apps

When an employee inputs a start date and an end date into the form Power Apps calculates the number of business days off. The definition of a business day is any date Monday-to-Friday and is not a company holiday. We will start by finding the number of business days excluding weekends.



Write this code in the Default property of the Number Of Days text input. It generates a single column table of dates between the start date and the end date. Then it filters the table to exclude weekends and counts the remaining rows. There shorter ways to write the formula but how this formula works is more understandable. As a general rule, coding should always prioritize readability over brevity.

With(
    {
        // generate a one-column table of all dates between start date & end date
        varDateRange: ForAll(
            Sequence(dte_EndDate.SelectedDate - dte_StartDate.SelectedDate + 1),
            dte_StartDate.SelectedDate + Value - 1
        )
    },
    If(
        And(
            IsBlank(dte_StartDate.SelectedDate),
            IsBlank(dte_EndDate.SelectedDate)
        ),
        // show nothing if any date pickers are blank
        0,
        // include only dates Monday to Friday
        CountIf(
            varDateRange,
            Weekday(Value) in [2, 3, 4, 5, 6]
        )
    )
)




Setup A SharePoint List For Holiday Calendars

The business days calculation excludes company holidays but where do they come from? We need to build a SharePoint list to capture this information. Create a new SharePoint list called Holidays Calendar with the following columns.

  • Title (single-line text)
  • HolidayDay (date only)



Populate the SharePoint list with this sample data (Canadian Holidays).

TitleHolidayDate
New Year’s Day1/1/2022
Family Day2/21/2022
Good Friday4/15/2022
Victoria Day5/23/2022
Canada Day7/1/2022
August Long Weekend8/1/2022
Labour Day9/5/2022
Thanksgiving10/10/2022
Remembrance Day11/11/2022
Christmas Day (in-lieu of)12/27/2022
Boxing Day (in-lieu)12/28/2022





Exclude Holidays From The Business Days Calculation In Power Apps

Now that we have created a datasource with company holidays the next thing we must do is connect it to our app. Add the Holiday Calendar SharePoint list using the Data menu.



We already have some code to calculate business days excluding weekends so all that’s needed are a few extra lines to handle holidays.



Update the COUNTIF formula in the Default property of the Number Of Days text input. If the holiday date is found within the single column table being generated it does not get counted in the results.

With(
    {
        // generate a one-column table of all dates between start date & end date
        varDateRange: ForAll(
            Sequence(dte_EndDate.SelectedDate - dte_StartDate.SelectedDate + 1),
            dte_StartDate.SelectedDate + Value - 1
        )
    },
    If(
        And(
            IsBlank(dte_StartDate.SelectedDate),
            IsBlank(dte_EndDate.SelectedDate)
        ),
        // show nothing if any date pickers are blank
        0,
        // show only dates Monday to Friday and exclude holidays
        CountIf(
            varDateRange,
            And(
                Weekday(Value) in [2, 3, 4, 5, 6],
                Not(Value in 'Holiday Calendar'.HolidayDate)
            )
        )
    )
)




Validate Start Date & End Date Are Not On Weekends Or Holidays

Data validation is an important part of every Power Apps form design. In this case we want to ensure employees select a valid start date and end date. Otherwise, calculating the number of business days might not be possible. The criteria for valid dates are:

  • The date is not a weekend day (Saturday, Sunday)
  • The date is not a holiday
  • The start date must be before the end date
  • Both a start date and an end date must be selected




Write this code in the BorderColor property of the Start Date date picker.

If(
    And(
        Or(
            Weekday(Self.SelectedDate) in [1,7],
            Self.SelectedDate in 'Holiday Calendar'.HolidayDate,
            Self.SelectedDate > dte_EndDate.SelectedDate
        ),
        !IsBlank(dte_StartDate.SelectedDate),
        !IsBlank(dte_EndDate.SelectedDate)
    ),
    Red,
    Parent.BorderColor
)



Likewise, use this code in the BorderColor property of the End Date date picker.

If(
    And(
        Or(
            Weekday(Self.SelectedDate) in [1,7],
            Self.SelectedDate in 'Holiday Calendar'.HolidayDate,
            Self.SelectedDate < dte_StartDate.SelectedDate
        ),
        !IsBlank(dte_StartDate.SelectedDate),
        !IsBlank(dte_EndDate.SelectedDate)
    ),
    Red,
    Parent.BorderColor
)



Try out the data validation by inputting a few invalid date combination into the form. When we use an invalid date the date picker border should turn red to indicate an error.




Submit The Vacation Request Form Data

Our form is almost completed. The last feature we need to build is a way to submit the form. Create a new button with the text Submit and place it at the bottom of the form.



We want to make it so the Submit button can only be pressed once data validation is passed. Use this code in the DisplayMode property of the button to disable it when the data validation rules are not being followed.

If(
    Or(
        IsBlank(dte_StartDate.SelectedDate),
        Weekday(dte_StartDate.SelectedDate) in [1,7],
        dte_StartDate.SelectedDate in 'Holiday Calendar'.HolidayDate,
        IsBlank(dte_EndDate.SelectedDate),
        Weekday(dte_EndDate.SelectedDate) in [1,7],
        dte_EndDate.SelectedDate in 'Holiday Calendar'.HolidayDate,
        dte_StartDate.SelectedDate > dte_EndDate.SelectedDate
    ),
    DisplayMode.Disabled,
    DisplayMode.Edit
)



The submit button should also disappear from the form when it is no longer in new mode or edit mode. Write this code in the Visible property of the button.

frm_VacationRequest.DisplayMode<>DisplayMode.View



Then input this code into the OnSelect property to submit the form when the button is pressed.

SubmitForm(frm_VacationRequest)



Once the form is submitted we need to capture the last submitted record and display the form in view-only mode.



Use this code in the OnSuccess property of the form. The variable gblVacationRequest current stores the submitted record.

Set(gblVacationRequestCurrent, frm_VacationRequest.LastSubmit);
ViewForm(frm_VacationRequest);



Then input the variable name into the Item property of the form.

gblVacationRequestCurrent





Test The Completed Vacation Request Form

We’re all done! Test the Vacation Request form to make sure its working as expected.





Questions?

If you have any questions about Power Apps Calculate Business Days Excluding Weekends & Holidays please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Matthew Devaney

Subscribe
Notify of
guest
21 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Jon Russell
8 months ago

Hey Matthew,

Great post. I have added a Home icon on the page so that after a form is submitted, you can click the Home button and be taken back to a new form.

PowerFx forumla is:

OnSelect: NewForm(frm_VacationRequest)

Visible: frm_VacationRequest.DisplayMode <> DisplayMode.Edit

home button.JPG
Robin
8 months ago

Little nitpicky suggestion/improvement:

For counting you use:
CountRows(
// show only dates Monday to Friday and exclude holidays
Filter(
varDateRange,
Weekday(Value) in [2, 3, 4, 5, 6],
Not(Value in ‘Holiday Calendar’.HolidayDate) //<– new code
)

I’d use:
CountIf(
// show only dates Monday to Friday and exclude holidays
varDateRange,
Weekday(Value) in [2, 3, 4, 5, 6] &&
Not(Value in ‘Holiday Calendar’.HolidayDate)
)

But of course a great article. When I started with PowerApps I had lots of problems doing this kind of stuff – learn to solve this kind of problems and you’re on your way to solve really unique problems

Jason McAndrew
Jason McAndrew
6 months ago

Hi Matthew,

Great article!

It’s really interesting to see how others tackle this sort of thing.

I did something similar using power automate. I discovered the bank holiday API on the gov website. https://www.api.gov.uk/gds/bank-holidays/#bank-holidays.

This may be an alternative instead of maintaining a list of holidays.

Thanks
Jason

Alex Hernández
Alex Hernández
6 months ago

Is it possible to directly associate the “Requested by” field with the user who entered the vacation request? In that case we would not use that registration field but the SharePoint list must be updated with the user who entered the request.

loco
loco
5 months ago

Hi, how to calculate the resume working date that is one day after the End Date but excludes public holidays and weekends?
Thank you.

loco
loco
5 months ago

Hi Matthew, thank you for replying. The formula works for the weekends but how to exclude the holidays as well?

Desirae
Desirae
5 months ago

Is there any way to convert the days to hours? I have been searching everywhere with no luck. I want to enable employees to request a partial day.

Shrikant Sawant
Shrikant Sawant
3 months ago

Looks great, But in my case user is defining the working hours for periods like in attached image. need your help to resolve it

Season working hours.png
Jean D
Jean D
5 months ago

Hi MatthewThank you a lot for this. Very usefull and interesting. My question to you is, how do you take into account mid days? I mean, i just want to take 1.5 days, how to do this?
Example : I’m taking holidays from 23/06/2022 morning to 24/06/2022 mid days, which is 1.5 days. How to make it?

Also, when users send their request, it sends to a sharepoint list. Users in sharepoint list are members or visitors. How to avoid that a sharepoint member once goes to the list, won’t see other member data (holidays request) in the list?

loco
loco
5 months ago

Hi Matthew, is there any formula on how to add business days but excluding public holidays and weekends? The return value is a date instead of a number. I’ve been looking for it for weeks now yet to no avails. Please help me.
Thank you.

AJ Mont
AJ Mont
4 months ago

Hello, Great work! does this have a reporting section where the leaves are stored that can be categorized how many leaves are used by a person in a month? specifically eyeing to see how the ranges can be calculated or split into multiple rows to allow easier grouping.

Thanks,
AJ

Shrikant Sawant
Shrikant Sawant
3 months ago

Nice post Matthew. it helps a lot.
I have one tricky scenario. In my case user will define working hours per day in SharePoint list format as below:

From To hours
01/01/2022 04/02/2022 8
04/03/2022 09/30/2022 7
10/01/2022 12/31/2022 9

e. I have to calculate the ‘No of working hours’ between two selected dates excluding holidays and weekends (power app image).

I need to replace hardcoded 7 from the PowerApps code and take user-defined values mentioned by the user in the SharePoint list.

I think I have to update the section “Exclude Holidays From The Business Days Calculation In Power Apps” but where and how exactly I am not getting it? Can you please help to resolve it?

Capture.PNG
Last edited 3 months ago by Shrikant Sawant
Shrikant Sawant
Shrikant Sawant
3 months ago

Updated last post

Last edited 3 months ago by Shrikant Sawant
Gilmour
Gilmour
2 months ago

Hi Matthew, another sleek solution!

How can I calculate – given a start date – an end date occurring X business days after the start date?