You’re offline. This is a read only version of the page.
I am doing a vacation request application. On the main page the user selects a start date and an end date, what I require is that a list be generated from that range of dates without taking into account weekends and holidays.
For example: Start date: 1/28/2022 to 2/2/2028, I would expect the list to only give me: Friday, 28 January 2022 Monday, 31 January 2022 Tuesday, February 1, 2022 Wednesday, February 2, 2022.
I referenced a similiar solution on the forum, but I still can't get the result. Clear(colDays);; With( { wStart: StarDate.SelectedDate; wEnd: EndDate.SelectedDate }; ForAll( sequence( 1 + ((DateDiff(wStart;wEnd;Days)) * 5 - ((Weekday(wStart) - Weekday(wEnd))*2)) / 7 - Switch(Weekday(wEnd);7;1;0) - Switch(Weekday(wStart);1;1;0) - CountIf( holidays; DateValue(HolidayDate) >= wStart; DateValue(HolidayDate) <= wEnd)); With( { wDay: DateAdd( wStart; value-1; days ) }; Collect( colDays; {WeekDay: wDay} ) ) ) )
I hope someone can support me.
Thanks in advance
Great, just what I was looking for. Thank you for sharing.
Hi @Pablo88
Assume holidays are in a collection
Share
Report
All responses (
Answers (ClearCollect(
colHolidays1,
{HolidayDate: DateValue("01/01/2022")},
{HolidayDate: DateValue("02/01/2022")},
{HolidayDate: DateValue("02/14/2022")},
{HolidayDate: DateValue("03/15/2022")}
)
Leave Calculation will be
Clear(colDays);
ForAll(
Sequence(
DateDiff(
StartDate10.SelectedDate,
DateAdd(
EndDate10.SelectedDate,
1,
Days
),
Days
),
0,
1
),
If(
Weekday(
DateAdd(
StartDate10.SelectedDate,
Value
)
) > 1 && Weekday(
DateAdd(
StartDate10.SelectedDate,
Value
)
) < 7 && IsBlank(
LookUp(
colHolidays1,
DateValue(HolidayDate) = DateAdd(
StartDate10.SelectedDate,
Value
)
)
),
Collect(
colDays,
{
WeekDay: DateAdd(
StartDate10.SelectedDate,
Value
)
}
)
)
)
Output (As per this example, 2/1/2022 is removed)
Thanks,
Stalin - Learn To Illuminate
Hi @Pablo88
Please try this
Clear(colDays);
ForAll(
Sequence(
DateDiff(
StartDate10.SelectedDate,
DateAdd(EndDate10.SelectedDate,1,Days),
Days
),
0,
1
),
If(
Weekday(
DateAdd(
StartDate10.SelectedDate,
Value
)
) > 1 && Weekday(
DateAdd(
StartDate10.SelectedDate,
Value
)
) < 7,
Collect(
colDays,
{
WeekDay: DateAdd(
StartDate10.SelectedDate,
Value
)
}
)
)
)
Example 1
Example 2
#1
WarrenBelz
791
Most Valuable Professional
#2
MS.Ragavendar
410
#3
mmbr1606
275
Super User 2025 Season 2
