We have all been there, we need to check the difference between 2 dates, and if you ever had to implement this you would need to use some crazy mathematical equations using the ticks() expression. But now.. I’m not sure when this expression got added, but we can now use dateDifference() expression instead of using … Continue reading "Get the difference between two dates EASY"

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?