Skip Weekends and Holidays

[sammybridge]

I have a Flow which runs when a record in CDS is updated and the status is complete. This then sets the next stage of records to Open and gives them a Due Date. The Due Date is working out by taking today's date and adding on the SLA which each record has.

I need my Due Date to do 2 things however:

1. Exclude weekends - If the date falls on Saturday or Sunday, then the revised Due Date should be Monday. I have this part working but can't figure out how to get it with point 2
2. Skip holidays - I have a list of holidays saved in SharePoint and I need to make sure the Due Date doesn't land on any of these days. If it does, I then need to work out how many days of the SLA have been worked prior to the holidays starting and then add on the remainder to the first working day after the holidays. E.g. I have an SLA starting today for 10 days. But the holiday period runs from 27/01/2020 - 31/012020. This means before the holidays start I have 1 working day (not including today) and then starting from 03/02/2020 (the first working day after the holiday week) I have another 9 working days left to complete the task/record.

Thanks

[yashag2255]

Hi @sammybridge 

You will have to perform a set of calculations and build logic for this in the flow. Here is how you can do it: 

1. Create a variable to save the SLA. 

2. Add action to get items from the SharePoint list and here in the filter query, filter records where the starting of holiday date is after today and before the intended SLA. Now you can calculate the differences and point the dates accordingly by using the add days function. 

Hope this helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[sammybridge]

Hi @yashag2255 ,

Thanks for your reply. I can get this working but the problem is that the add days function only gives a different in days and not working days. Is there any way to get this in working days?

Thanks

[manuelstgomes]

Hi @sammybridge 

I've discovered the solution to this issue for another user here on the community, and I have written a blog article that explains the solution in detail. Check it out here:
https://manueltgomes.com/microsoft/flow/find-next-business-day/

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
​
Cheers
Manuel

[sammybridge]

Hi @manuelstgomes , 

This looks good but unfortunately it is using a premium connector that I can't use for my client. I have managed to cheat it a little bit to get a SP calculated column to do the bit where it skips weekends, but I'm struggling to skip by holidays.

Thanks

[sgrpdl]

Hi! @sammybridge  any luck? Did you able to solve this issues? 

As, I am too looking for similar one.. kindly share you sol~ 

Thank you in Adv!

[sgrpdl]

I solved it here is the sol~