Answered

Generate a list of dates derived from a range of dates

(0) Share

Report

Posted on by Pablo88

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

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (2)

Sort by

Verified answer

StalinPonnusamy Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Pablo88 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you very much.

Work very well.

Can you help me, excluding the holidays please.
I have a collect with the dates.

Was this reply helpful? Yes No
Verified answer

StalinPonnusamy Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Pablo88

Assume holidays are in a collection
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

Was this reply helpful? Yes No
Pablo88 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @StalinPonnusamy .
Thank you very much.

Was this reply helpful? Yes No
jorge.daniel 1,430 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Great, just what I was looking for. Thank you for sharing.

Was this reply helpful? Yes No