How to Calculate Business Days Excluding Weekends Holidays in Power Apps?

Last week, I built a Power Apps Leave Management application. Within that, there is a leave request form that allows the employees to request leave. Here, I was required to calculate the leave days between the start and end dates, excluding public holidays and weekends.

In this article, I will explain how to calculate business days excluding weekends holidays in Power Apps and how to add a half day leave.

Calculate Working Days Between Start and End Dates Power Apps

Here, I have an Employee Leave Request Form that allows employees to submit their leave requests to their manager in the organization.

To find out the number of days they are taking leave, I took the leave days field, which calculates the leave days between the start date and end date; if an employee wants to take half a day, that will also be included, and it is considered as 0.5 days.

While calculating the number of days here I’m excluding weekends. You can refer to the example below, which calculates working days by excluding weekends.

For this Power Apps form, I took the SharePoint list as a data source named Employee Leave Requests.

This list includes the following columns.

Column Name	Data Type
Leave Title	Title(Single line of text)
LeaveType	Choice(Sick Leave, Annual Leave, Compensate Leave, Maternity Leave,Others)
StartDate	Date and time
EndDate	Date and time
HalfDay	Yes/No
LeaveDays	Number
ManagerName	Single line of text
Department	Choice(IT, HR, Marketing,Sales)
Comments	Multiline of text
ManagerEmail	Single line of text

Follow the steps below to calculate the number of leave days between the start date and the end date, including half days and excluding weekends.

1. Connect Power Apps with SharePoint list-> Add an Edit Form control and provide the connected SharePoint list name to its Data Source property.

2. In the Power Apps form, open the data card value of the Leave Days field and provide the formula below in its Default property.

With(
    {
        varDateRange: ForAll(
            Sequence(DataCardValue_EndDate.SelectedDate - DataCardValue_StartDate.SelectedDate + 1),
            DataCardValue_StartDate.SelectedDate + Value - 1
        )
    },
    If(
        IsBlank(DataCardValue_StartDate.SelectedDate) || IsBlank(DataCardValue_EndDate.SelectedDate),
        0,
        DataCardValue_StartDate.SelectedDate = DataCardValue_EndDate.SelectedDate && DataCardValue_HalfDay.Value,
        0.5,
        // Calculate business days 
        CountRows(
            Filter(
                varDateRange,
                Weekday(Value) in [
                    2,
                    3,
                    4,
                    5,
                    6
                ]
            )
        ) +
        // Add half day if toggle is on
        If(
            DataCardValue_HalfDay.Value,
            0.5,
            0
        )
    )
)

Here,

• DataCardValue_EndDate = End date field data card value name.
• DataCardValue_StartDate = Start date field data card value name.
• DataCardValue_HalfDay = Hald day field data card value name.

The above code will do the following things:

• It displays the leave days as 0 if both start and end dates are empty
• If the start and end dates are the same and we turn on the half day, the leave days will display 0.5
• It won’t consider the weekdays between the start and dates.
• The Power Apps Sequence function will generate a table with numbers from 1 to number difference between start and end dates.
• The Power Apps ForAll function will produce a table of dates from the start date to the end date.
• The With function in Power Apps allows us to create variables; here, the varDateRange variable stores the forall function returned table values.
• The Power Apps WeekDay function returns the week day provided date.
• The Power Apps Filter function filters the dates stored in varDateRange table values with weekdays between 2 to 6, which are working days.
• The Power Apps CountRows function will return the number of records in the filter function returned table.

3. Now, save the changes and preview the app once. it will calculate the number of leave days between the start date and the end date, excluding weekends and including half days.

Power Apps Calculate Working Days Excluding Public Holidays

While calculating leave days to exclude the public holidays, we need to store all the public holidays in a data source; here, I stored them in a SharePoint list named Public Holidays.

Look at the example below. Now, the number of leave days excludes public holidays and weekends. It also includes half-day.

Follow the steps below to achieve this!

1. First, connect the Public Holidays SharePoint list with Power Apps. Then, provide the formula below in the Default property of leave days field data card value.

With(
    {
        varDateRange: ForAll(
            Sequence(DataCardValue_EndDate.SelectedDate - DataCardValue_StartDate.SelectedDate + 1),
            DataCardValue_StartDate.SelectedDate + Value - 1
        ),
        // List of public holidays
        varPublicHolidays: 'Public Holidays'.HolidayDate
    },
    If(
        IsBlank(DataCardValue_StartDate.SelectedDate) || IsBlank(DataCardValue_EndDate.SelectedDate),
        0,
        DataCardValue_StartDate.SelectedDate = DataCardValue_EndDate.SelectedDate && DataCardValue_HalfDay.Value,
        0.5,
    // Calculate business days
        CountRows(
            Filter(
                varDateRange,
                Weekday(Value) in [
                    2,
                    3,
                    4,
                    5,
                    6
                ] && !(Value in varPublicHolidays)
            )
        ) +
        // Add half day if toggle is on
 If(
            DataCardValue_HalfDay.Value,
            0.5,
            0
        )
    )
)

Here, ‘Public Holidays’ is the SharePoint list name and HolidayDate is the column name within that list.

2. Now, save changes and preview the app. You can see that the leave days will exclude weekends and public holidays.

In this way, we can calculate the leave days by excluding the weekends and public holidays.

Error: The first argument to Sequence must be between 0 and 50,000 Power Apps

While calculating leave days between start and end dates, if the start date is greater than the end date, we’ll get the error as: The first argument to Sequence must be between 0 and 50,000.

This error indicates that the sequence value must be between 0 and 50,000. Which means the sequence won’t allow negative values.

Previously, in the code, we subtracted the start date from the end date. When the end date is less than the start date, we’ll get negative values, which are reflected as an error here.

Solved: The first argument to Sequence must be between 0 and 50,000 Power Apps

To solve this error, replace the formula below with the previous formula in the leave days data card value’s Default property.

If(
  //If dates would result in negative Number.
 !IsBlank(DataCardValue_StartDate.SelectedDate) && !IsBlank(DataCardValue_EndDate.SelectedDate) && (DataCardValue_StartDate.SelectedDate > DataCardValue_EndDate.SelectedDate),
 " ",

With(
    {
        varDateRange: ForAll(
            Sequence(DataCardValue_EndDate.SelectedDate - DataCardValue_StartDate.SelectedDate + 1),
            DataCardValue_StartDate.SelectedDate + Value - 1
        ),
        varPublicHolidays: 'Public Holidays'.'Holiday Date'
    },
    If(
        IsBlank(DataCardValue_StartDate.SelectedDate) || IsBlank(DataCardValue_EndDate.SelectedDate),
        0,
        DataCardValue_StartDate.SelectedDate = DataCardValue_EndDate.SelectedDate && DataCardValue_HalfDay.Value,
        0.5,
        CountRows(
            Filter(
                varDateRange,
                Weekday(Value) in [
                    2,
                    3,
                    4,
                    5,
                    6
                ] && !(Value in varPublicHolidays)
            )
        ) + If(
            DataCardValue_HalfDay.Value,
            0.5,
            0
        )
    )
))

In the above formula, the if condition in starting will take a null value if the start and end dates are empty or if the start date is greater than the end date.

Follow the steps below to indicate to the employee that the end date must be greater than the start date.

1. Provide the text below in the Text property of Error Message of End Date DataCard.

"End Date must be greater than or equal to start date"

2. To make the error message visible only when we select the end date is less than the start date. Provide the below formula in the Visible property of the EndDate_DataCard‘s Error Message label.

If(DataCardValue_EndDate.SelectedDate>= DataCardValue _Start Date .Selected Date ,false ,true)

Now, save all the changes and preview the app once. You’ll see that when I select the end date less than the start date, instead of getting a sequence error, our custom error is displayed. This will indicate to the employees to select the end date greater than the start date.

This way, we can display a custom error message if the end date value is less than the start date to avoid the sequence error message.

I hope you understand how to calculate leave days between the start date and the end date in Power Apps by excluding weekends and public holidays.

Here, I also explained how to solve the sequence error, which occurs when we select the end date less than the start date. You can follow this article while you’re also calculating working days, and if your requirements are slightly different, try to change the code according to your needs.

Also, you may like:

• How to get dates for the current week in Power Apps
• Format date in Power Apps data table
• Power Apps date functions: Now, Today, IsToday, UTC functions
• Power Apps search function
• Generate and Download PDFs From Power Apps