Generate a list of dates derived from a range of dates

[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

[StalinPonnusamy · Accepted solution]

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

[Pablo88]

Thank you very much.

Work very well.

Can you help me, excluding the holidays please.

I have a collect with the dates.

[StalinPonnusamy · Accepted solution]

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

[Pablo88]

Hello @StalinPonnusamy .

Thank you very much.