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?

This is a little one, but sort of cool. I’m building a Power App which takes orders for shipment between stores. The person filling out the form can request a delivery date, but we want to give them a decent default. (It would be great if we could set minimum or maximum date in a date picker, but we can’t.)

Generally, something requested today can be delivered tomorrow, so we want a tomorrow which is smart about weekends, regardless what day of the week it is.

I came up with this little formula to set the DefaultDate for the form’s Requested Delivery Date date picker:

If(
     Weekday(Now()) = 6, // Friday
     Today() + 4,
     Weekday(Now()) = 7, // Saturday
     Today() + 3,
     Weekday(Now()) = 1, // Sunday
     Today() + 2,
     Today() + 1
 ) 

Like in Excel, the Weekday function returns the day’s number, where

Result	Meaning
1	Sunday
2	Monday
3	Tuesday
4	Wednesday
5	Thursday
6	Friday
7	Saturday

The net-net of the formula is:

If

  Today is Friday, the following Tuesday

Else

  Today is Saturday, the following Tuesday

Else

  Today is Sunday, the following Tuesday

Else

  Tomorrow

This won’t stop people from picking a bad date, but it will put them in the right place to start.

We will probably make this fancier to handle things like times after noon, but this is a good starting point for a smarter date picker.