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