by So you want to calculate the difference between 2 days, start looking for a Power Automate to do it, and can’t find one? There isn’t one, but that doesn’t mean we cannot calculate it ourselves. Today, look at calculating the difference between two dates in Power Automate.
Set up an example.
Let’s look at the following example to understand better what’s happening. We have two dates. The first one is today, represented by the “utcNow” function, and the other is six days from now using the “addDays” function. Here’s what it looks like with two “Compose” actions.
We add the days as follows:
addDays(utcNow(),6)
The formula
Now let’s look at the formula that we have to achieve this:
div(div(sub(ticks(outputs('Six_more_days_since_today')),ticks(outputs('Today'))), 10000000), 86400)
So what’s happening here? Let’s work our way inside out.
ticks(outputs('Six_more_days_since_today'))
and
ticks(outputs('Today'))
The ticks function will convert both dates into “standard” terminology from a reference day. This avoids timezones, date formats, and other problems with parsing dates.
The ticks function will return the number of 100-nanosecond from a reference day, meaning the more significant the number, the later the date. And since they are numbers, we can remove one number from the other to know the difference between them in 100-nanosecond increments. Let’s use the “sub” function to achieve this.
sub(ticks(outputs('Six_more_days_since_today')),ticks(outputs('Today'))
Now we have the difference between the two dates. Notice that we’re putting the more significant date first to have a positive number (remember, the later the date, the higher the number will be).
Now that we have the number of 100-nanosecond between the two dates, let’s convert that into something that makes more sense. Days, for example. First, let’s convert it to seconds to get a better number:
div(..., 10000000)
To do that, we need to divide by 10,000,000 (a nanosecond is 1,000,000,000, but since the number represents increments of 100-nanosecond hence the value) to get the number of seconds.
Then let’s divide it further to get the days.
div(..., 86400)
A day is 86400 seconds or 24(hours) x 60 (minutes) x 60 (seconds).
Let’s test to see what we get:
The result is 6, which makes sense since it’s a six days difference like we added in the past.
What about days, hours, and seconds?
We can use the same strategy as before but reduce the value of the division. We started with 24(hours) x 60 (minutes) x 60 (seconds), so if we want to calculate the hours, we need to:
60 (minutes) x 60 (seconds) = 3600
Here’s the formula:
div(div(sub(ticks(outputs('Six_more_days_since_today')), ticks(outputs('Today'))), 10000000), 3600)
The same for the minutes:
div(div(sub(ticks(outputs('Six_more_days_since_today')), ticks(outputs('Today'))), 10000000), 60)
And for the seconds, we don’t even need the division since the first one already returns the seconds.
div(sub(ticks(outputs('Six_more_days_since_today')), ticks(outputs('Today'))), 10000000)
Final thoughts
I wrote way more than expected, but I wanted you to understand the formula and how each step worked. Now that you have the formula, you can use it in your Flows and get the results, while Microsoft doesn’t provide us with an official function to do it.
Photo by Claudio Schwarz on Unsplash
