cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IsaacM22
Regular Visitor

calculate business days in workflow, exclude weekends and holidays

Hi!

 

I'm creating a workflow of reminders but I'm kinda stuck at the moment. 

 

I have created this list:

listSP.png

 

and until now I'm testing by triggering the workflow manually but it's going to be a scheduled workflow. The workflow consists of sending a reminder 3 days in advance, 1 day in advance and on the same day as the date entered in the field (Compliance date and / or report). Until there, everything is fine!:

listSP.png

What has been difficult for me is how to calculate what the working days are, in this case Monday through Friday. I need to exclude Saturdays, Sundays and holidays.

 

¿What should i do to make it work?

Could you please help me! and I really appreciate it.

2 REPLIES 2
haroldbk_msft
Community Support
Community Support

I have worked on a flow that will calculate the end date given the start date and the number of working days. This formula will exclude weekends. You might be able to use this to get the working days. Another option would be to use an unattended UI Flow and run a calculation in Excel to return the number of working days.

https://docs.microsoft.com/en-us/power-automate/ui-flows/overview

 

haroldbk_msft_0-1598305681715.png

This is the expression in Compose 2 - Compose 3 outputs the end date.

 

 

if(equals(dayOfWeek(adddays(if(equals(dayOfWeek(variables('startdate')),6),addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5)))),6), addDays(adddays(if(equals(dayOfWeek(variables('startdate')),6), addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5))),2), if(equals(dayOfWeek(adddays(if(equals(dayOfWeek(variables('startdate')),6), addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5)))),0), adddays(adddays(if(equals(dayOfWeek(variables('startdate')),6), addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5))),1), adddays(if(equals(dayOfWeek(variables('startdate')),6), addDays(variables('startdate'),2), if(equals(dayOfWeek(variables('startdate')),0), adddays(variables('startdate'),1),variables('startdate'))), add(mul(7,div(variables('duration'),5)), mod(variables('duration'),5)))))

Thank you very much for your answer.

Until now I have had no success to exclude weekends at least.

 

I've done this:

 

SP.png

 

In the "compose" block, I have this expression:  

dayOfWeek(utcNow()) and returns the day of the week. in the condition valid that.
 
the flow begins by sending a reminder email, for example. (An email must be sent 4 days in advance) then, if the date is for ("09-01-2020" Tuesday). then the reminder email should be sent today ("08-26-2020" Wednesday). Counting business days (Thursday, Friday, Monday and Tuesday). I can't validate that, it doesn't exclude Saturday and Sunday and by not excluding the weekend, the mail is sent for Sunday, which cannot be the case.
 

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,816)