You are currently viewing How PowerApps add business days to date Excludes Weekends and Holidays
add business days to date Excludes Weekends and Holidays

How PowerApps add business days to date Excludes Weekends and Holidays

  • Post author:
  • Post category:Power Apps
  • Reading time:4 mins read

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

  1. 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

  1. 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 .

  1. 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
    )
)

date | Power Platform Geeks
See Also
Join us
Need Help
  • Have a related question? Please ask it at deBUG.to Community.

Heba Kamal

Power Platform Consultant and Founder of Power Platform Geeks