In this blog post we will learn how PowerApps add business days to date and Excludes the Weekends and the company Holidays from that date

PowerApps add working days to date

In this example we will learn how to add 2 business days to specific date and exclude the holidays and weekends, suppose that the week start from Monday and ends on Friday.

The holidays stored in a SharePoint list as shown in bellow image,

powerapps add business days to date — PowerApps Exclude Holiday days from date

First We will count the holidays that its date is between our specific date and the date after 2 business days.

CountIf(
            HolidaysCollection,
            StartDate >= DatePicker1.SelectedDate,
            StartDate <= DatePicker1.SelectedDate + 3  
        )

// here I add 3 to the selected date to get the date after adding 2 business days

How PowerApps add business days to date Excludes Weekends

Next we will Exclude the week ends from the date , Suppose that the Week start with Monday and end with Friday, to exclude only the weekends we can use this formula

With(
    {
        varStartDate: DatePicker1.SelectedDate,
        varAddBuisnessdays: 2
    },
    DateAdd(
        varStartDate,
        Value(varAddBuisnessdays) + RoundDown(
            (Weekday(
                varStartDate,
                Monday
            ) + Value(varAddBuisnessdays) - 1) / 5,
            0
        ) * 2,
        Days
    )
)

This will add 2 business days and exclude only the weekends from our date .

So in order to Exclude both weekends and holidays from the date, we will use this formula to add the formula of the Holidays to Your Start date, the result will be a date that add number of business days to your date Excludes Weekends and Holidays.

With(
    {
        varStartDate: DatePicker1.SelectedDate + CountIf(
            HolidaysCollection,
            StartDate >= DatePicker1.SelectedDate,
            StartDate <= DatePicker1.SelectedDate + 3
        ),
        varAddWeekdays: 2
    },
    DateAdd(
        varStartDate,
        Value(varAddWeekdays) + RoundDown(
            (Weekday(
                varStartDate,
                Monday
            ) + Value(varAddWeekdays) - 1) / 5,
            0
        ) * 2,
        Days
    )
)