Patch only business days from a date range

[Calin_Mateut]

Hello! I have a problem.

I want to patch in the SharePoint list 'Calendar' only the working days from the date range between StartDatePicker and EndDatePicker.

Example provided:

StartDatePicker = 03 Aug 2023
EndDatePicker = 29 Aug 2023

The list as it should look in the SP list, from data range per week:

Week1. 03 Aug - 04 Aug 2023
Week2. 07 Aug - 11 Aug 2023
Week3. 14 Aug - 18 Aug 2023
Week4. 21 Aug - 25 Aug 2023
Week5. 28 Aug - 29 Aug 2023

The patch formula should look like this:

Patch(
Calendar, // My SharePoint list name
Defaults(Calendar),
{
UserID: varEmployee.ID,
ShiftID: LookUp(ColShifts, Title = ShiftSelector_EditScreen.Selected.Title, ID),
GroupID: varEmployee.GroupID0,
StartDate:

EndDate:
}

Is there any solution to this problem? Many thanks in advance!

[TheRobRush]

This is gonna be a lot, but it will help you understand the process better.

First Off somewhere in your app, onvisible of the screen its used, or even just onstart of the app itself place this

ClearCollect(
    //Obligatory entertaining collection name
    HolidayCelebrate,

        ForAll(
                
                //This makes our formula that follows pull every holiday from 5.5 years before and after the current date
                Sequence(4015,-2007,1),

                With(
                    {dateCheck: DateAdd(Today(),ThisRecord.Value,TimeUnit.Days)},
                        If(
                            
                            dateCheck
                            in
                [
                    ///OBSERVED STATIC HOLIDAYS (MONDAY AFTER FOR THOSE FALLING ON A WEEKEND)
                    //January 1st
                    Switch(
                            Weekday(DateValue(Concatenate("01/01/",Year(dateCheck)))),
                            7,DateAdd(DateValue(Concatenate("01/01/",Year(dateCheck))),2,TimeUnit.Days),
                            1,DateAdd(DateValue(Concatenate("01/01/",Year(dateCheck))),1,TimeUnit.Days),
                            DateValue(Concatenate("01/01/",Year(dateCheck)))),
                    //Juneteenth / Emancipation Day        
                    Switch(
                            Weekday(DateValue(Concatenate("06/19/",Year(dateCheck)))),
                            7,DateAdd(DateValue(Concatenate("06/19/",Year(dateCheck))),2,TimeUnit.Days),
                            1,DateAdd(DateValue(Concatenate("06/19/",Year(dateCheck))),1,TimeUnit.Days),
                            DateValue(Concatenate("06/19/",Year(dateCheck)))),
                    //July 4th / Independence Day
                    Switch(
                            Weekday(DateValue(Concatenate("07/04/",Year(dateCheck)))),
                            7,DateAdd(DateValue(Concatenate("07/04/",Year(dateCheck))),2,TimeUnit.Days),
                            1,DateAdd(DateValue(Concatenate("07/04/",Year(dateCheck))),1,TimeUnit.Days),
                            DateValue(Concatenate("07/04/",Year(dateCheck)))),
                    //Veterans Day / Armistice Day
                    Switch(
                            Weekday(DateValue(Concatenate("11/11/",Year(dateCheck)))),
                            7,DateAdd(DateValue(Concatenate("11/11/",Year(dateCheck))),2,TimeUnit.Days),
                            1,DateAdd(DateValue(Concatenate("11/11/",Year(dateCheck))),1,TimeUnit.Days),
                            DateValue(Concatenate("11/11/",Year(dateCheck)))),
                    //Christmas Day
                    Switch(
                            Weekday(DateValue(Concatenate("12/25/",Year(dateCheck)))),
                            7,DateAdd(DateValue(Concatenate("12/25/",Year(dateCheck))),2,TimeUnit.Days),
                            1,DateAdd(DateValue(Concatenate("12/25/",Year(dateCheck))),1,TimeUnit.Days),
                            DateValue(Concatenate("12/25/",Year(dateCheck)))),
                    ///ACTUAL STATIC HOLIDAYS
                    //IF YOU ONLY WANT THE ACTUAL HOLIDAY NOTMONDAY AFTER REMOVE THE ABOVE AND USE THIS
                    /*
                    DateValue(Concatenate("01/01/",Year(dateCheck))),
                    DateValue(Concatenate("06/19/",Year(dateCheck))),
                    DateValue(Concatenate("07/04/",Year(dateCheck))),
                    DateValue(Concatenate("11/11/",Year(dateCheck))),
                    DateValue(Concatenate("12/25/",Year(dateCheck))),
                    */

                    ///VARIABLE HOLIDAYS
                    //////3RD MONDAY JANUARY MLK DAY
                    With({FirstOfMonth:DateValue(Concatenate("01/01/",Year(dateCheck))),
                          DayOfWeek:2,
                          WeekNumber:3
                          },
                          DateAdd(FirstOfMonth,(WeekNumber * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek)))
                        ),
                    //////3RD MONDAY FEBRUARY GEORGE WASHINGTON BIRTHDAY
                    With({FirstOfMonth:DateValue(Concatenate("02/01/",Year(dateCheck))),
                          DayOfWeek:2,
                          WeekNumber:3
                          },
                          DateAdd(FirstOfMonth,(WeekNumber * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek)))
                        ),
                    //////LAST MONDAY IN MAY MEMORIAL DAY
                    With({NextMonth:DateValue(Concatenate("06/01/",Year(dateCheck)))
                          },
                          DateAdd(NextMonth,-1 * Weekday(DateAdd(NextMonth, - 2)))
                        ),
                    //////1ST MONDAY SEPTEMBER LABOR DAY
                    With({FirstOfMonth:DateValue(Concatenate("09/01/",Year(dateCheck))),
                          DayOfWeek:2,
                          WeekNumber:1
                          },
                          DateAdd(FirstOfMonth,(WeekNumber * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek)))
                        ),
                    //////2ND MONDAY OCTOBER COLUMBUS / INDIGENOUS PEOPLES DAY
                    With({FirstOfMonth:DateValue(Concatenate("10/01/",Year(dateCheck))),
                          DayOfWeek:2,
                          WeekNumber:2
                          },
                          DateAdd(FirstOfMonth,(WeekNumber * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek)))
                        ),
                    //////4TH THURSDAY IN NOVEMBER THANKSGIVING
                    With({FirstOfMonth:DateValue(Concatenate("11/01/",Year(dateCheck))),
                          DayOfWeek:5,
                          WeekNumber:4
                          },
                          DateAdd(FirstOfMonth,(WeekNumber * 7) - Weekday(DateAdd(FirstOfMonth,7-DayOfWeek)))
                        ),
                ] 
                || Weekday(dateCheck) = 1
                || Weekday(dateCheck) = 7, 
                
                    {
                        Holiday: DateValue(dateCheck, "en-US")
                    }
                        )
                        
                    )
                )
            )

This will give you a list of every holiday and workday for like 5 years on either side of your current date.

Now in OnChange of the DatePickers you want 

ClearCollect(
    workDayList,
            ForAll(
                    Sequence(DateDiff(DatePicker1.SelectedDate,DatePicker2.SelectedDate,TimeUnit.Days)+1,0) As checkDates, 
                    With(
                    {dateCheck: DateAdd(DatePicker1.SelectedDate,checkDates.Value,TimeUnit.Days)},
                        If(
                            Not(
                            dateCheck
                            in
                            HolidayCelebrate),
                                            
                    {
                        workDay: DateValue(dateCheck, "en-US"),
                        weekNum: ISOWeekNum(DateValue(dateCheck, "en-US"))
                    }))))

This will provide you with a list of dates not located in the holiday/non workday list aka the working days, as well as the week of the year they fall on.

You can then place the following in a gallery to see how you can structure that data to give you start and end dates of each range

ForAll(GroupBy(workDayList, "weekNum", "workWeek") As weekExample, {startDay: First(weekExample.workWeek).workDay, endDay: Last(weekExample.workWeek).workDay})

*Edit changed to IsoWeek

[TheRobRush · Accepted solution]

*Edit Above, totally forgot about IsoWeek, so if you tried the formula above before I posted the edit, change it to the new formula above for the OnChnage of DatePickers to correct the 2024/2025 transition

[Calin_Mateut]

Thank you!, @TheRobRush ! 
At the moment I don't need the holidays, but I found a part that helped me a lot after I adapted it.
I wanted to take only my days from Monday to Friday (weekday 1-5).
Below is the code for my situation which I am posting to help others who need the same thing.

On OnChange EndDatePicker I set the following formula:

//Collect dates in date range in workDayList collection

ClearCollect(
workDayList,
ForAll(
Sequence(Value(DateDiff(DatePicker1.SelectedDate, DatePicker2.SelectedDate, TimeUnit.Days) + 1), 0) As checkDates,
With(
{dateCheck: DateAdd(DatePicker1.SelectedDate, checkDates.Value, TimeUnit.Days)},
If(
Not(dateCheck in HolidayCelebrate) && Weekday(DateValue(dateCheck, "en-US"), StartOfWeek.Monday) >= 1 && Weekday(DateValue(dateCheck, "en-US"), StartOfWeek.Monday) <= 5,
{
workDay: DateValue(dateCheck, "en-US"),
weekNum: WeekNum(DateValue(dateCheck, "en-US"), StartOfWeek.Monday),
weekDay: Weekday(DateValue(dateCheck, "en-US"), StartOfWeek.Monday)
}
)
)
)
);
// Collect StartDate and EndDate for each week in colBusinessDays
ClearCollect(
colBusinessDays,
ForAll(
GroupBy(
workDayList,
"weekNum",
"workWeek"
) As weekExample,
{
StartDay: First(weekExample.workWeek).workDay,
EndDay: Last(weekExample.workWeek).workDay
}
)
);

And OnSelect submit button I set the following part of code:

ForAll(
colBusinessDays,
Patch(
Calendar, // Your SharePoint list name
Defaults(Calendar),
{
UserID: varEmployee.ID,
ShiftID: LookUp(ColShifts, Title = ShiftSelector_EditScreen.Selected.Title, ID),
GroupID: varEmployee.GroupID0,
StartDate: StartDay,
EndDate: EndDay
}
)
)

Thank you again! Have a good day!