calculate business days in workflow, exclude weekends and holidays

(0) Share

Report

Posted on by IsaacM22

Hi!

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

I have created this list:

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!:

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.

Categories:

Using flows

I have the same question (0)

All responses (2)

Answers (0)

haroldk on at

Like (1)

Report

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

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)))))

Was this reply helpful? Yes No
IsaacM22 3 on at

Like (0)

Report

Thank you very much for your answer.
Until now I have had no success to exclude weekends at least.

I've done this:

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.

Was this reply helpful? Yes No