I had a recent ask for how to calculate dates in Power Automate, specifically, given a StartDate and Schedule calculate the NextScheduledDate. 📅
The choices for Schedule consisted of Weekly, Monthly, Quarterly, Biannually and Annually.
I created two flows, one to do the calculation 🧮, called Date Add Child Flow (Automated), and another, called Date Add Child Flow (Automated) Test, to test my flow, because, of course, testing is important!
I started with a PowerApps (V2) Trigger as I was planning to call this flow from other flows and/or from a Canvas App.
I then added a Variable action and declared variable varNextScheduledDate as type String, unfortunately there is not a Date type.
I then added a Switch action with a whole bunch of Cases, not my favorite approach as it gets a bit congested, but gets the job done. 👌
What’s the secret sauce to calculating varNextScheduledDate? I already gave it away in the title, it’s the addToTime function.
I added the following Case statements with Variable action to calculate varNextScheduledDate based on the passed in Start Date and Schedule.
- Weekly –
addToTime(triggerBody()['date'], 7, 'Day', 'M/d/yyyy') - Monthly –
addToTime(triggerBody()['date'], 1, 'Month', 'M/d/yyyy') - Quarterly –
addToTime(triggerBody()['date'], 3, 'Month', 'M/d/yyyy') - Biannually –
addToTime(triggerBody()['date'], 6, 'Month', 'M/d/yyyy') - Annually –
addToTime(triggerBody()['date'], 1, 'Year', 'M/d/yyyy')
Lastly, I added a PowerApps (V2) action to return the value of varNextScheduleDate as a Date, but in order to do that, I had to first convert it to a Date, using parseDateTime(variables('varNextScheduledDate'),'en-US', 'M/d/yyy').
After completing the flow, I created my test flow, ran it, and everything passed! 🥳
Test it out by downloading the solution at https://mattruma.com/wp-content/uploads/2024/04/DateAddSolution_1_0_0_1.zip and importing it into your Power Platform environment.