Calculate the Difference Between Two Dates in Power Automate
This is a pretty quick one, but very useful, nonetheless. I had a lot of trouble finding a way to determine the number of days between two dates for a flow I was building. After a lot of Binglage, I found an article (Calculate the difference between dates in a Power Automate Flow) from Phil Cave (@philcave) that got me to the solution. So, to give Phil more exposure for his helpful article and in the hopes that I’ll find my own post later, I’m cribbing some of it here. (Note: it appears that both Phil and I like T. Rex.)
In my case, I was building a scheduled flow. This is a flow which will run on a regular schedule to do work for us.
The trigger for a scheduled flow isn’t based on someone taking some sort of action, like updating a list item or uploading a document. Instead, we set its schedule and the flow fires up on that schedule. (The trigger is called Recurrence, even though they are called scheduled flows. Erg.)
For example, here’s a trigger set to run every morning at 6am in my time zone, which is Eastern Time.
Usually when I build a solution with Power Apps and Power Automate, I end up with some flows which trigger based on user actions and at least one scheduled flow. Scheduled flows are great for things like:
- Reminding people of something they need to do on a regular basis – especially if they haven’t taken any actions
- Creating a “report” about a set of content
- Cleaning up old content
- etc.
In this flow, I wanted to determine how many days it has been since someone has updated a list item. The Power App collects customer feedback, and we want to be sure we act on that feedback within a set period of time. Thus, I wanted to find the number of days between Today and Modified.
Phil’s article set me on the right path.
First, I initialized a variable called daysSinceChange. Then, inside the Apply to each for each list item which meets my other criteria, I set that variable.
The formula is based on the one in Phil’s post:
div(sub(ticks(formatDateTime(utcNow(),'yyy-MM-dd')),ticks(item()?['Modified'])),864000000000)It’s a bit of a mouthful, so let’s break it down.
First, I get the current moment – utcNow() in ticks:
ticks(formatDateTime(utcNow(),'yyy-MM-dd'))and the Modified date/time in ticks:
ticks(item()?['Modified'])What are ticks, you might ask? A tick is a 100-nanosecond interval. Converting a date/time to ticks yields the number of 100-nanosecond intervals since January 1, 0001 12:00:00 (midnight). By calculating the difference between the two date/times in that unit, we have a lot of flexibility. See Reference guide for functions in expressions – Azure Logic Apps for more info.
And guess what else? Lots of programming languages use the ticks concept. Here’s a post I wrote all the way back in 2013 explaining how to do this with the ddwrt library in Data View Web Parts: Calculate Days between Two SharePoint List Dates in XSL Using ddwrt:DateTimeTick.
Next, I subtract the number of ticks for the Modified date/time from now. That tells me how many ticks it’s been since the last update.
sub(ticks(formatDateTime(utcNow(),'yyy-MM-dd')),ticks(item()?['Modified']))Finally – since ticks aren’t that useful in this context, I divide the number of ticks by the crazy number 864000000000.
Why 864000000000? Well, there are 100 * 100 * 100 * 60 * 60 * 24 ticks in one day. Thus, simply a conversion of ticks to days.
Next, I have a condition that acts as a filter based on how many days it has been since the last update. In this case, I want to do specific things at 1, 5, 10, or 20 days.
Ticks are fun! We don’t even really need to understand what they are, as long as we understand that every date/time can be represented in ticks and we know how to convert from ticks to a useful unit of measure.
Addendum: Ivan Wilson (@IvanWilson) pointed me to his article Microsoft Flow – formula to calculate the due date excluding weekends on Twitter. Nice one! It’s a horrible looking formula: wouldn’t it be nice if Power Automate had a function for this?
Hi Marc, I have gone a step further and wrote a Child Flow to calculate working days i.e. Mon-Fri going forwards and backwards and Bank Holidays if you require it. Not sure if its the most efficient way as very new to Power Automate. Can share this for you and your readers if need be?
@Ian:
Absolutely! Let’s collect all the goodness.
M.
Marc you always have such great blog posts! And if Ian would like to share his post, that would be terrific.
Hey Marc, long time no see. :)
Should the date format in ticks not be ‘yyyy-MM-dd’ (four digit year) instead of ‘yyy-MM-dd’ (three digit year)? Phil’s post has the same 3 digit year format.
@Ed:
Nice to hear from you! I just did a test with a simple button flow using both
yyyandyyyy. I got the same answer in both cases:div(sub(ticks(formatDateTime(utcNow(),'yyyy-MM-dd')),ticks(addDays(utcNow(),10))),864000000000)As weird as it is, checking the docs both give the four digit year, unless you’re in a year < 1000. Why anyone would want that is beyond me.
M.
Thank you Marc! So, what if I want to check for time elapsed for a specific column of the list items? For example, I want to check if it’s been a certain amount of time since a column has been set to a certain value.
@Andy:
That’s a different challenge. The Modified date/time just tells us that something changed: there’s no column level date/time. To do this, you’ll want to save some into in the item (or elsewhere) when your criteria is met and then track against that.
M.
Marc, great post helped a lot but something is off in your ticks calculation:
100 * 100 * 100 * 60 * 60 * 24
calculates to:
86 400 000 000
In your expression and explanation you use the number:
864 000 000 000
I looked it up and the calculation should be:
10 * 1000 * 1000 = 1second * 60 = minute * 60 = hour * 24 = day.
1 second = 1000 milliseconds
1 millisecond = 1000 microseconds
Hi I assume I can use this to test if a file has been added to a library in the last day and then create an HTML list with only those files.
You could. It might make more sense to only retrieve those files which have been updated in the last 24h.
why do we need to do this way when there is the function in power automate called dateDifference(startdate,enddate)?
dateDifference may well serve your purposes, as long as the format it provides works for you. From the current documentation:
This example subtracts the first value from the second value:
dateDifference(‘2015-02-08’, ‘2018-07-30’)
And returns this result: “1268.00:00:00”
How do you split to get the days only?
Hello, can I see the whole flow?
@Alex:
The whole flow contained a lot of other logic which isn’t relevant to the post. Plus, it was client-specific.
M.
How would I achieve the following…
I want to capture when a app service client secret or certificate is due to expire within the next 30 days.
Currently this is in the code but it does not make any sense to me? what does the 100, 1000000000, 3600 mean?
24 just so you know is how many days to expiry but its not working.
div(div(div(mul(sub(outputs(‘EndTimeTickValue’),outputs(‘StartTimeTickValue’)),100),1000000000) , 3600), 24)
I guess its comparing startdate time of the certificate and then the end date time but at the moment I am also capturing certificates that are already expired which I dont want.
I only want the certificates and secrets that are active but are due to expire within the next 30 days?
How would I code this using ticks?
That formula doesn’t match mine, so it’s hard for me to tell what you’re trying to do. If you look at my example, it shows how to figure out how many days have passed since a given time. Your need is similar, but you want to see how many days until the certificate expires.
If I read your question correctly, this might work:
`div(sub(ticks(outputs(‘EndTimeTickValue’)),ticks(outputs(‘StartTimeTickValue’))),864000000000)`
That should give you the number of days until the certificate expires.