A user was challenged with the following scenario -
A project request is entered via Microsoft Forms, and the user wanted to use Microsoft Power Automate / Flow to automatically add tasks to Planner. The big issue comes from the Due Date on the project request: If it's more than 7 days out, then the task due date will be 2 days before that...if it's less than 2 days out, then the Task Due Date will be the same, and if it's in between the Task Due Date will be one day before the Project Due Date.
Date math in Power Automate is just...weird. It uses something called "Ticks" to count the number of 100 nanosecond intervals between that date and New Year's Day, 1601. I am totally not kidding.
As you can imagine, this will produce some very large numbers and require some creative math, especially for those of us who don't frequently work with dates and programming.
Compounding the issue, the mechanism which PA / Flow uses to store and pass data (JSON), has a limit on how big an integer can be. If you exceed that, it switches things to a "string", which makes math a step more difficult. More on that later.
Compounding things even further, is that, behind the scenes, most date stuff uses Coordinated Universal Time (UTC).
If you've ever had an issue with a date looking correct inside the flow, but not showing correctly on the front end (D365, Planner, etc...) it is most likely because of a conversion (or lack of) to or from UTC.
Setting Things Up
Before I get too far into building something, I like to sketch it out and walk the user through it. This gives us an opportunity to talk about the different opportunities there will be for things to break, and help us consider the most effective path and best journey for their user/customer.
In this case, the final iteration included the Tick math and variable setup (more as a reminder to myself).
When I first approached this issue, I had no idea how we would solve it. I knew the pieces were out there and I just had to find a way to mash them into the solution I was trying to build. The point is, don't be intimidated by big problems. Break them into smaller and smaller pieces and take the next step until something breaks...and it will. You've got this, though.
The first piece I found was this article where a user found a way to do this by creating two variables (one for each date) and then a third variable which was the difference and included the tick-math.
A Word on Variables
What this post didn't include was a primer on how variables work. Sean Bugler described it perfectly to me this way, "First, you create the box...then you put something in the box."
The first step with variables is to 'initialize' it (create the box)...but you need to define what kind of box it will be. There are a few choices, but the two we're interested in right now are "String" and "Integer". Think of "String" as a free form text field, ready to accept just about anything, whereas "Integer" is waiting just for a number. You can put a number in a String box, but doing math with it later will take an extra step. I like to define the box as the thing I will want it to be later, so I began with these being Integers and then ran into some trouble when there were too many 100 nanosecond intervals for the JSON to handle as a number, and I got the following error:
BadRequest. The variable 'varUTCNowTicks' of type 'Integer' cannot be initialized or updated with value '636965753936295074' of type 'String'. The variable 'varUTCNowTicks' only supports values of types 'Integer'
More on that later...
Synchronize Your Watches
Digging in, I used a manual trigger instead of a Form to make the testing easier. You can input information directly from there, as well.
The next step was to convert the local time in the input to the same time the system will be using everywhere else. Using the built-in function called "Convert Timezone", I also formatted the date/time to just the date.
Build a Box
initializing the variables needs to happen at the top of your Flow. You'll see here that I created a fourth variable to be the resultant, "Task Due Date"
Setting our respective code-paradigms aside for a moment, initializing the variables required some simple expressions to get us on the right path. If you work fine in Excel, even at a basic level, you'll do fine here.
As I mentioned before, the original tick-math generated too large of a figure for JSON to consider as 'still a number'. So I had to divide the number of ticks between now ("UTCNow()") and 01 January 1601 by 864 Billion (the number if ticks in a day) as I'm defining the variable, to keep it as a number. The expression looks like "DIV(ticks(utcNow()),864000000000)".
The expression for the DueDateTicks uses the dynamic data from the trigger, "DIV(ticks(body('Convert_Due_Date_to_UTC')) ,864000000000)" and the DateDiff variable just does the math, "sub(variables('varTicksDue'),variables('varUTCNowTicks'))".
The TaskDueDate variable is going to remain a date, so I initialized that as a String and set it to the DueDate once it had been converted to UTC (shows as "Converted Time" in the dynamic data).
I Just Dropped In...
...to see what condition my condition was in.
Now that the variables are all set, we'll look at the result of our DateDiff subtraction and make a decision for the TaskDueDate based on that.
Remember:
If the DateDiff is 7 or more, the TaskDueDate will be 2 days less.
If the DateDiff is 2-6 days out, then it will be 1 day less.
Any sooner, and the TaskDueDate will be the same as the Project Due Date.
There are a few ways to nest the conditions, but I chose to begin with asking if the DateDiff is 7 or more. If yes, then we use the "Subtract from Time" to pull two days off, and then "Set Variable" to update the TaskDueDate.
If the TaskDueDate is less than 7 days, then we ask another question to see if it is less than or equal to two days. If it is, we leave it set to the Project Due Date (nothing in the second "Yes" path), otherwise (in the "No" path) we use the same method as above to remove one day and update the variable.
Do the Thing
Finally, we can create the task in Planner. Or can we?
Planner is expecting a specific date format. If you get it wrong, it will yell at you with the following:
"The request is invalid:\r\nCannot convert the literal '2019-07-01T17:00:00' to the expected type 'Edm.DateTimeOffset'.",
"innerError": {
Planner is looking for a standardized (ISO) date in UTC. I am fairly certain there is an expression to manage the same thing, but I use the "Convert Timezone" again to update the format (pay attention to the upper/lower case...it matters):
John Liu helped me out with this primer on ISO Dates:
yyyy-MM-ddTHH:mm:ss <- local time, undefined timezone
yyyy-MM-ddTHH:mm:ssZ <- utc time (global machine time)
2019-06-19T23:35:33+00:00 <- timezone at UTC 0 offset
2019-06-19T23:35:33+10:00 <- local time, sydney (+10) offset
"so the ground rule with date time is always capture user specified time (if they tell you 8 AM) as local time in their timezone, convert that to UTC for all date math. When you're ready to send back to the user, convert it back to their timezone. If you are saving to planner, or SharePoint, Exchange, use UTC time - it's all stored that way."
Now, we can add all of the things to the task in Planner.
Note that the "Title" came from the original trigger and the Due Date is just the 'converted' TaskDueDate.
Planner will adjust the date for the user's local timezone.
This is just one way to solve this particular problem, and hopefully it was able to help you learn a few things along the way. I know I did.