Exclude weekends and holidays in Date Picker

[AstroDogN64]

I have an SP list custom Power Apps form and I need the date range to exclude weekends and holidays. For example, if a user picks a date that is Saturday, set the date to Friday but a prompt letting them know that they picked a weekend/holiday is acceptable too.

Function isWeekday(date as Date) as Boolean return Weekday(date) >= 1 and Weekday(date) <=5 End Function

If(DataCardValue20.Selected.Value="Yes",If(DataCardValue15.Selected.Value ="green", DateAdd(DataCardValue23.SelectedDate,-1), If(DataCardValue15.Selected.Value="blue",DateAdd(DataCardValue23.SelectedDate,-2), If(DataCardValue15.Selected.Value="yellow",DateAdd(DataCardValue23.SelectedDate,-3), If(DataCardValue15.Selected.Value="orange",DateAdd(DataCardValue23.SelectedDate,-4), If(DataCardValue15.Selected.Value="red",DateAdd(DataCardValue23.SelectedDate,-5), ))))))

The function didn't work, I tried putting it in OnChange too but nothing. Any help or guidance for the best way to accomplish this is greatly appreciated.

[LaurensM · Accepted solution]

@AstroDogN64 I found a possible solution that resets the date to the previous Friday when the user selects Saturday or Sunday. Holidays will be a bit trickier.

• Reset the date to Friday when Saturday or Sunday is selected

To achieve this we will need to create a variable (on the OnVisible of your screen) for the Default property of your date picker

Set(varDefaultDate, Today());

(You will also have to reset it after the SubmitForm / patching to SP)

Should the date picker be in a data card (form), follow these 3 steps first: Unlock the data cards by (1) clicking on the card, (2) going to the 'advanced' tab in the property panel on the right and (3) unlocking the card. This will allow us to set the Default property of the data card to varDefaultDate.

Set the OnChange property of the date picker to the code below. I have added comments to further explain those steps

If(
// Check if weekend is selected
    Weekday(
        Self.SelectedDate,
        StartOfWeek.Monday
    ) > 5,
    Notify(
        "Please select a weekday",
        NotificationType.Error
    );
//Set the date to the previous Friday
    Set(
        varDefaultDate,
        DateAdd(
            Self.SelectedDate,
            -(Weekday(
                Self.SelectedDate,
                StartOfWeek.Monday
            ) - 5),
            Days
        )
    );
// Reset to make the card show the (now changed) default value
    Reset(Self)
)

Excluding holidays is currently not supported, as is also mentioned here. What you could do is create a collection with the Holidays in your country and check if the selected date is present in that table. If that is the case, just change the date to the next weekday.

If this solves your question, would you be so kind as to accept it as a solution.
Thanks!

[AstroDogN64]

It works, thank you!

[AstroDogN64]

It seems like when the date picker is auto populated by this formula, the auto populate can still pick a weekend. Is there a way to avoid this?

Function isWeekday(date as Date) as Boolean return Weekday(date) >= 1 and Weekday(date) <=5 End Function

If(DataCardValue20.Selected.Value="Yes",If(DataCardValue15.Selected.Value ="green", DateAdd(DataCardValue23.SelectedDate,-1),

[AdminofMk]

Dear Mr. @LaurensM ,

 I am facing issue in excluding weekends(Friday and Saturday) in my date range,

can you help me to achieve this.

currently i am using this formula but no luck.

RoundDown(
    DateDiff(
        Sdate.SelectedDate,
        Ndate.SelectedDate,
        Days
    ) / 7,
    0
) * 5 + Mod(
    5 + Weekday(
        Ndate.SelectedDate,
        StartOfWeek.Sunday
    ) - Weekday(
        Sdate.SelectedDate,
        StartOfWeek.Sunday
    ),
    5
) - CountIf(
    Holidays,
    Date >= Sdate.SelectedDate,
    Date <= Ndate.SelectedDate
)