cancel
Showing results for 
Search instead for 
Did you mean: 
nagaraja2026

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

 

 

 

Comments
About the Author
  • 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.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/