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 App
 •  Setup A SharePoint List For Vacation Requests
 •  Create A New Canvas App In Power Apps Studio
 •  Insert An Edit Form To Capture Vacation Start & End Dates
 •  Calculate The Number Of Business Days Excluding Weekends In Power Apps  
 •  Setup A SharePoint List For Holiday Calendars
 •  Exclude Holidays From The Business Days Calculation In Power Apps
 •  Validate Start Date & End Date Are Not On Weekends Or Holidays
 •  Submit The Vacation Request Form Data
 •  Test 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).

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.