Add 30 working days excluding weekends and bank holidays

Question

I have a date picker control. When the use selects a date, I want a textbox to give 30 working days later, excluding weekends and bank holidays. This is what I have at the moment.

Switch (CountIf(BankHolidayDates, BankHolidayDate >= 

StartDate.SelectedDate, BankHolidayDate <= DateAdd(StartDate.SelectedDate,
Value(gvDaysToDeadline)  +
RoundDown(gvDaysToDeadline / 5, 0)*2+
Switch(Weekday(StartDate.SelectedDate),5,If(Mod( gvDaysToDeadline , 5)>0,2,0),
                                           4,If(Mod( gvDaysToDeadline , 5)>1,2,0),
                                           3,If(Mod( gvDaysToDeadline , 5)>2,2,0),
                                           2,If(Mod( gvDaysToDeadline , 5)>3,2,0),
                                           1,If(Mod( gvDaysToDeadline , 5)>4,2,0))
)),0,DateAdd(StartDate.SelectedDate,
Value(gvDaysToDeadline)  +
RoundDown(gvDaysToDeadline / 5, 0)*2+
Switch(Weekday(StartDate.SelectedDate),5,If(Mod( gvDaysToDeadline , 5)>0,2,0),
                                           4,If(Mod( gvDaysToDeadline , 5)>1,2,0),
                                           3,If(Mod( gvDaysToDeadline , 5)>2,2,0),
                                           2,If(Mod( gvDaysToDeadline , 5)>3,2,0),
                                           1,If(Mod( gvDaysToDeadline , 5)>4,2,0))
),1,DateAdd(StartDate.SelectedDate,
Value(gvDaysToDeadline+1)  +
RoundDown((gvDaysToDeadline+1) / 5, 0)*2+
Switch(Weekday(StartDate.SelectedDate),5,If(Mod( gvDaysToDeadline+1 , 5)>0,2,0),
                                           4,If(Mod( gvDaysToDeadline+1 , 5)>1,2,0),
                                           3,If(Mod( gvDaysToDeadline+1 , 5)>2,2,0),
                                           2,If(Mod( gvDaysToDeadline+1 , 5)>3,2,0),
                                           1,If(Mod( gvDaysToDeadline+1 , 5)>4,2,0))
),2,DateAdd(StartDate.SelectedDate,
Value(gvDaysToDeadline+2)  +
RoundDown((gvDaysToDeadline+2) / 5, 0)*2+
Switch(Weekday(StartDate.SelectedDate),5,If(Mod( gvDaysToDeadline+2 , 5)>0,2,0),
                                           4,If(Mod( gvDaysToDeadline+2 , 5)>1,2,0),
                                           3,If(Mod( gvDaysToDeadline+2 , 5)>2,2,0),
                                           2,If(Mod( gvDaysToDeadline+2 , 5)>3,2,0),
                                          1,If(Mod( gvDaysToDeadline+2 , 5)>4,2,0))
))

Answer 1

You can use something similar to the expression below:

With(
    { next9Weeks: ForAll(
        Sequence(63), // Next 9 weeks
        DateAdd(DatePickerCanvas1.SelectedDate,Value)) },
    With(
        {
            nextBusinessDays: Filter(
                next9Weeks,
                Weekday(Value, StartOfWeek.Monday) <= 5  // Monday-Friday
                    And Not (Value in bankHolidays)) },  // Exclude bank holidays
        Index(nextBusinessDays, 30).Value))

Where bankHolidays is a single-column collection / table with the dates for the holidays.

If there is the possibility of more than 15 bank holidays in any 9-week period you will need to increase the number of days to look ahead.