cancel
Showing results for 
Search instead for 
Did you mean: 

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”

 

image.png

 

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.

 

image.png

 

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

 

 image.png

 

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.

 

image.png

 

 

 

 

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.

 

image.png

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

 

 

 

Meet Our Blog Authors
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Cambridge UK Power Platform User Group Leader, Technical evangelist and speaker. Always says yes to coffee! #LetsGetCoffee
  • Passionate #Programmer #SharePoint #SPFx #Office365 #MSFlow | C-sharpCorner MVP | SharePoint StackOverflow, Github, PnP contributor
  • I am building business processes and applications that are easy for users' to stick to, so they can follow and understand them. In overall I transform processes to be more reliable and effortless. I am a proud co-organizer of SharePoint Saturday Warsaw and active community member, blogger and international speaker.