Exclude weekends and holidays in Leave Request flow

Question

Hope I get some guidance.

I have a leave request working almost good (after 3 month trying all sort of things).

Employee request a leave (SP List1) --------> Approver receives an email to approve or reject ----------> If approve updates a record holding balances for employees (SP List2) ------> Sends an approved email to employee with

current balance --------> Creates an event in a group calendar. This step is where I have the issue. The calendar does not exclude weekends or holidays.

How can I exclude Weekends and Public holidays (I have created a third list for the public holidays but don't have a clue on what's next).

We have a total of 22 vacation days or pay leave in a year.

Please heeeeelllllllpppp!

Thank You.

Answer 1

There may be a better way, but here is my approach. This is just conceptual, so you'd obviously need to adapt this to suit your specific needs.

1. Identify Start and End dates
2. varDate: Initialize a string variable with start date as the value
3. varDateArray: Initialize an array variable containing an object that consists of varDate and the day of the week for the start date. Day of the week expression: @{dayOfWeek(variables('varDate'))}
4. Set varDate to the last date in varDateArray plus 1 day. Expression: @{addDays(last(variables('varDateArray'))?['Date'],1)}
5. Append the new date to varDateArray
6. Repeat 4 & 5 until varDate is equal to the End date
7. Filter varDateArray to only include weekend days. (Sunday = 0, Saturday = 6). Expression: or(equals(item()?['DayOfWeek'], 0), equals(item()?['DayOfWeek'], 6))
8. Set as a new Array variable varInvalidDates
9. Get the public holidays from your list and append the dates to varInvalidDates
10. Filter varDateArray. Formula = varInvalidDates does not contain item()

The output of this should be all the dates from the Start date to the End date, without Saturdays, Sundays and any public holidays specified in your list. Hopefully you can take parts of this and make it work in your flow.

1. Your trigger would be different to this. The key thing is to get the start and end dates.

2 & 3.

4,5 & 6.

7 & 8.

9 & 10.