Date Ranges and working day calculation PowerApps, Leave Management (Chinese Holiday Calendar)

Scenario:

User is applying for leaves by selecting Start Date and End Date with date pickers. You have to calculate working days between the two dates. General formula being:

Working Days = (End Date – Start Date)- Weekends – Holidays

Points to note:

1. Exclude weekends.
2. Exclude holidays (holidays are in ranges. Refer to calendar here for more/sample information).
3. Working weekends.

Implementation:

During a PowerApps development assignment recently, I came across this scenario. It was unique, new and felt challenging.

How did I set up?

Created a SharePoint Online development tenant. Created master lists for- Holidays, Weekend Working and User Leave Requests.

∗∗∗Microsoft has provided a sample template here. It has useful resources and rich in information.

Date functions from PowerApps resources does help a lot here.

Functionality to calculate working dates between two dates is fairly simple. Refer to this article and the sample template shared above.

The trick and challenge awaits us when we are doing Holiday calculation. You can create a new collection if “Holidays” are individual days similar to weekend working items. However, if the holidays are in ranges (example above) you are in for a treat and a good one too.

This post really helped in building the base on the calculation. Added a wee bit of my brain and eureka… a working solution is available. Here is the magical code:

Clear(HolidayDateRange);
ForAll(
    HolidaysCalendar,
    Collect(
        HolidayDateRange,
        AddColumns(
            FirstN(
                [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29],
                DateDiff(HolidayStartDate, HolidayEndDate, Days) + 1),
            "Day",
                "Day " & (Value + 1),
            "Date",
                DateAdd(HolidayStartDate, Value, Days)
        )
    )
)

I have used the “OnVisible” event of my welcome screen to populate this collection.

Here is the variable which stores the Holidays falling in my leave date selection..condition- should be a working day.

Set(_holidaysInRequest, CountIf(HolidayDateRange, Date >= LeaveStartDatePicker.SelectedDate, Date <= LeaveEndDatePicker.SelectedDate,Weekday(Date) in [2,3,4,5,6]));

Last but not the least, the duration calculation:

Set(_requestedDays, (_workDaysInRequest +_workingWeekendsInRequest) - (_holidaysInRequest))

Variable “_workDaysInRequest” has only the weekdays (no weekends).

#HappyToHelp #Community #SharingIsCaring #GivingBack