Formula to exclude weekends and public holidays

[syhrh]

Hi, I have a Sharepoint form to fill in to apply leave. My company has a policy that Annual leaves can only be applied 3 days in advance for approval. So I want to restrict the user if the date they apply is less than 3 days, then a warning will come out stating that cannot apply for Annual Leave as it doesn't meet the requirement of 3 days. The leave type column is Choice so the user can choose the leave type.

the dataCardValue2 is the Start Date and dataCardValue17 is End Date

I manage to create the warning label that meets the condition after 3 days but it includes the weekends and public holidays. I want the label to exclude weekends and public holidays. I tried the formula like the picture above but it doesn't work. May I know how to fix it?

Thank you.

[v-xiaochen-msft]

Hi @syhrh ,

Please take a look at this post.

https://powerusers.microsoft.com/t5/Building-Power-Apps/Calculate-the-working-days-by-subtracting-we...

Best Regards,

Wearsky

[syhrh]

Hi @v-xiaochen-msft, from the post, I've replaced my formulas with yours. From that post, there's end date and start date for the holidays but I have only start date so I deleted the end date. But I have errors. I don't really understand that post, does it return date or days?

[TheRobRush]

@syhrh  I assume you are looking for a self contained method, and I have set one up for you. The method linked here I believe depends on a holiday table stored somewhere to reference. 

Below I have created two formulas that will look at a datepicker1 & datepicker2 and determine how many days between them are work days IE) not saturday, sunday, or a holiday. All self contained so no need for a reference table.

The Holidays referenced are American Holidays, you may need to adjust regionally but it is all notated out well enough

FORMULA 1, INCLUDES DAY OF FIRST DATEPICKER

CountRows(
Filter(

ForAll(
///START DATE RANGE, 
            Sequence(
                    DateDiff(
		///SET START DATE
                            DatePicker1.SelectedDate,
        ///SET END DATE
                            DateAdd(DatePicker2.SelectedDate,1,Days),
        ///RETURN DIFFERENCE IN DAYS
                            Days
                            )
                    ),
        If(And(
                ////THIS CALCULATES IF THE DAY IN QUESTION IS A WEEKEND
                Weekday(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value-1))<>1,
                Weekday(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value-1))<>7,
                ////THIS CALCULATES IF THE DAY IN QUESTION IS A HOLIDAY
	            //// ADJUST REGIONALLY AS NEEDED
                Not(Text(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value-1),"[$-en-US]mmdd")
                                        
                                            in 
                                            [
                                                ///STATIC HOLIDAYS

                                                ///0101 NEW YEARS DAY
                                                ///0619 EMANCIPATION JUNETEENTH
                                                ///0704 INDEPENDENCE DAY
                                                ///1111 VETERANS DAY
                                                ///1225 CHRISTMAS DAY
                                                0101,0619,0704,1111,1225,

                                                ///FLOATING HOLIDAYS

                                                ///3RD MONDAY JANUARY MLK DAY
                                                Text(
                                                With({FirstOfMonth:
                                                DateValue(
                                                            Concatenate(
                                                                        "01/01/",
                                                                        Year(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value-1)
                                                                            )
                                                                        )
                                                        ),
                                                        DayOfWeek:2,
                                                        N:3
                                                      },
                                                        DateAdd(FirstOfMonth,
                                                        (N * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek))
                                                                )
                                                    ),"[$-en-US]mmdd"),

                                                ///3RD MONDAY FEBRUARY GEORGE WASHINGTON BIRTHDAY
                                                Text(
                                                With({FirstOfMonth:
                                                DateValue(
                                                            Concatenate(
                                                                        "02/01/",
                                                                        Year(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value-1)
                                                                            )
                                                                        )
                                                        ),
                                                        DayOfWeek:2,
                                                        N:3
                                                      },
                                                        DateAdd(FirstOfMonth,
                                                        (N * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek))
                                                                )
                                                    ),"[$-en-US]mmdd"),
                                                     
                                                ///1ST MONDAY SEPTEMBER LABOR DAY
                                                Text(
                                                With({FirstOfMonth:
                                                DateValue(
                                                            Concatenate(
                                                                        "09/01/",
                                                                        Year(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value-1)
                                                                            )
                                                                        )
                                                        ),
                                                        DayOfWeek:2,
                                                        N:1
                                                      },
                                                        DateAdd(FirstOfMonth,
                                                        (N * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek))
                                                                )
                                                    ),"[$-en-US]mmdd"),
                                                     
                                                ///2ND MONDAY OCTOBER COLUMBUS / INDIGENOUS PEOPLES DAY
                                                Text(
                                                With({FirstOfMonth:
                                                DateValue(
                                                            Concatenate(
                                                                        "10/01/",
                                                                        Year(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value-1)
                                                                            )
                                                                        )
                                                        ),
                                                        DayOfWeek:2,
                                                        N:2
                                                      },
                                                        DateAdd(FirstOfMonth,
                                                        (N * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek))
                                                                )
                                                    ),"[$-en-US]mmdd"),
                                                     
                                                ///4TH THURSDAY IN NOVEMBER
                                                Text(
                                                With({FirstOfMonth:
                                                DateValue(
                                                            Concatenate(
                                                                        "11/01/",
                                                                        Year(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value-1))
                                                                        )
                                                        ),
                                                        DayOfWeek:5,
                                                        N:4
                                                      },
                                                        DateAdd(FirstOfMonth,
                                                        (N * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek))
                                                                )
                                                    ),"[$-en-US]mmdd"),
                                                     
                                                ///LAST MONDAY IN MAY
                                                Text(
                                                With({NextMonth:
                                                DateValue(
                                                            Concatenate(
                                                                        "06/01/",
                                                                        Year(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value-1)
                                                                            )
                                                                        )
                                                        )},
                                                        DateAdd(
                                                                NextMonth, -1 * Weekday(DateAdd(NextMonth, - 2))
                                                                )
                                                    ),"[$-en-US]mmdd")               
                                            ]))=true,
                                            ///SAVE THE DATE
                                            DateAdd(DatePicker1.SelectedDate,ThisRecord.Value-1))),
                                            ///FILTER OUT NULL ENTRIES
                                            IsBlank(Value)=false))

FORMULA 2 does NOT count day of first date picker

CountRows(
Filter(

ForAll(
///START DATE RANGE, 
            Sequence(
                    DateDiff(
		///SET START DATE
                            DatePicker1.SelectedDate,
        ///SET END DATE
                            DatePicker2.SelectedDate,
        ///RETURN DIFFERENCE IN DAYS
                            Days
                            )
                    ),
        If(And(
                ////THIS CALCULATES IF THE DAY IN QUESTION IS A WEEKEND
                Weekday(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value))<>1,
                Weekday(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value))<>7,
                ////THIS CALCULATES IF THE DAY IN QUESTION IS A HOLIDAY
	            //// ADJUST REGIONALLY AS NEEDED
                Not(Text(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value),"[$-en-US]mmdd")
                                        
                                            in 
                                            [
                                                ///STATIC HOLIDAYS

                                                ///0101 NEW YEARS DAY
                                                ///0619 EMANCIPATION JUNETEENTH
                                                ///0704 INDEPENDENCE DAY
                                                ///1111 VETERANS DAY
                                                ///1225 CHRISTMAS DAY
                                                0101,0619,0704,1111,1225,

                                                ///FLOATING HOLIDAYS

                                                ///3RD MONDAY JANUARY MLK DAY
                                                Text(
                                                With({FirstOfMonth:
                                                DateValue(
                                                            Concatenate(
                                                                        "01/01/",
                                                                        Year(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value)
                                                                            )
                                                                        )
                                                        ),
                                                        DayOfWeek:2,
                                                        N:3
                                                      },
                                                        DateAdd(FirstOfMonth,
                                                        (N * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek))
                                                                )
                                                    ),"[$-en-US]mmdd"),

                                                ///3RD MONDAY FEBRUARY GEORGE WASHINGTON BIRTHDAY
                                                Text(
                                                With({FirstOfMonth:
                                                DateValue(
                                                            Concatenate(
                                                                        "02/01/",
                                                                        Year(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value)
                                                                            )
                                                                        )
                                                        ),
                                                        DayOfWeek:2,
                                                        N:3
                                                      },
                                                        DateAdd(FirstOfMonth,
                                                        (N * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek))
                                                                )
                                                    ),"[$-en-US]mmdd"),
                                                     
                                                ///1ST MONDAY SEPTEMBER LABOR DAY
                                                Text(
                                                With({FirstOfMonth:
                                                DateValue(
                                                            Concatenate(
                                                                        "09/01/",
                                                                        Year(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value)
                                                                            )
                                                                        )
                                                        ),
                                                        DayOfWeek:2,
                                                        N:1
                                                      },
                                                        DateAdd(FirstOfMonth,
                                                        (N * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek))
                                                                )
                                                    ),"[$-en-US]mmdd"),
                                                     
                                                ///2ND MONDAY OCTOBER COLUMBUS / INDIGENOUS PEOPLES DAY
                                                Text(
                                                With({FirstOfMonth:
                                                DateValue(
                                                            Concatenate(
                                                                        "10/01/",
                                                                        Year(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value)
                                                                            )
                                                                        )
                                                        ),
                                                        DayOfWeek:2,
                                                        N:2
                                                      },
                                                        DateAdd(FirstOfMonth,
                                                        (N * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek))
                                                                )
                                                    ),"[$-en-US]mmdd"),
                                                     
                                                ///4TH THURSDAY IN NOVEMBER
                                                Text(
                                                With({FirstOfMonth:
                                                DateValue(
                                                            Concatenate(
                                                                        "11/01/",
                                                                        Year(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value))
                                                                        )
                                                        ),
                                                        DayOfWeek:5,
                                                        N:4
                                                      },
                                                        DateAdd(FirstOfMonth,
                                                        (N * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek))
                                                                )
                                                    ),"[$-en-US]mmdd"),
                                                     
                                                ///LAST MONDAY IN MAY
                                                Text(
                                                With({NextMonth:
                                                DateValue(
                                                            Concatenate(
                                                                        "06/01/",
                                                                        Year(DateAdd(DatePicker1.SelectedDate,ThisRecord.Value)
                                                                            )
                                                                        )
                                                        )},
                                                        DateAdd(
                                                                NextMonth, -1 * Weekday(DateAdd(NextMonth, - 2))
                                                                )
                                                    ),"[$-en-US]mmdd")               
                                            ]))=true,
                                            ///SAVE THE DATE
                                            DateAdd(DatePicker1.SelectedDate,ThisRecord.Value))),
                                            ///FILTER OUT NULL ENTRIES
                                            IsBlank(Value)=false))

I used two date pickers for testing purposes, but you can feel free to replace the first datepicker date, with the date of the current day to calculate how far away their submission request is.