Calculating working days in Power Automate

Requirement :

When the sales representative moves the quote to “At Customer” stage (means a quote is sent to a customer) , a phone call should be created automatically with due date as below.

• The activity should set the Due date to three days ahead.
• For example, If a quote is sent on a Monday the due date should be set to Thursday.
• It should account for Saturdays and Sundays so if the quote is sent on a Wednesday the due date should not be set to Saturday. It should instead be set to Monday.

The challenge

Given a start date and the number of working days we need to add, calculate a target date.

The logic works like this:

• Add one day at a time to our start date – let’s call this the running date.
• If the running date is not a weekend or a public holiday, increment a counter.
• Repeat until the counter equals the number of working days we need to add.
• Set the target date to the final value of the running date.

The trigger

Here the trigger is when the BPF record is updated and the active stage is “At Customer”

Here GUID is unique id of “Active Customer Stage”. To know the GUID of any stage , we need to query “processstages” entity and pass the business process flow id and stage name as filter.

OrgURI/api/data/v9.1/processstages?$select=stagename,processstageid&$filter=_processid_value eq {BPFGUID} and stagename eq 'At Customer'

The variables

We need to define a few variables at the top:

• counter  – we’ll use this to keep track of how many qualifying days we’ve notched up in our loop.
• runningDate – is the date we’re going to add days to in our loop. We’re initially setting this to the .
• runningDateIncremented  – is a variable we’ll use to store temporary values when we’re calculating dates.

The loop

To do the calculation, we are going to use a Do Until loop. We want to run the loop until the value of our counter, counter, is equal to the number of working days we need to add . The loop looks like this

For clarity, in advanced mode, the loop condition reads @equals(variables(‘Counter’), 3).

The next thing we need to do is add one day to our running date. We need to do this with two Set variable activities:

• Set runningDateIncremented to addDays(runningDate,1).
• Set runningDate to runningDateIncremented.

The end result – we’ve added one day to runningDate.

The weekday check

Checking whether a specific date is a weekday is straightforward inPower Automate. We use the dayOfWeek function. This returns a number – 0 is Sunday, 6 is Saturday, and anything in between is a weekday.

If the condition is false, we do nothing and let the loop go back to the start. If the condition is true, we need to increment the counter using ‘Increment Variable’ action.

The result

When the counter reaches the target days to add (i.e 3 in my case) , our loop will stop running. At this point, our running date is the date we need – i.e Due Date We can use ‘Create a new record’ action to create phone call activity record in CDS/CRM.

We can extend this by checking the date is a public holiday or not.

That’s it. Hope it helps.  Original post is from my blog.