Power Automate: ticks Function

The “ticks” function is beneficial when it comes to parsing dates. Since dates are so messy to convert due to timezones, leap years, daylight saving, to name a few, there was a need to generate a standard date and time that can be comparable regardless of where the person is.

The “ticks” function returns a number corresponding to 100-nanosecond intervals (talk about precision 😀) since the 1st January 0001 midnight. The number is quite significant, but, typically we won’t look at it, as we’ll see in the examples below.

Let’s check how to use the function efficiently.

Usage

It follows a simple pattern.

1. String with a date in ISO format

Let’s start with a simple example, where we convert on 27th September 2021.

ticks('2021-09-27T13:44:35.0796736Z')

will return 

637683470750796800

It’s a huge number, so what can we do with it? Let’s see, for example, how to convert a date into an Excel number. The formula is as follows:

add(div(sub(ticks('2021-09-27T13:44:35.0796736Z'),ticks('1900-01-01T00:00:00Z')),864000000000),2)

will return:

44466

Let’s check a bit further what we have:

1. We convert both dates to ticks. We have a standard reference
2. We use the sub function to get the difference between those dates
3. We then use the div function to get the days since the ticks are in 100-nanosecond intervals.
4. Finally, we add 2 to the date due to Excel’s “feature” where they consider 1900 as a leap year (it’s not). I have an article about it, and Microsoft also has a great explanation on why they can’s fit it.

The ticks function enables a standard reference for a date regardless of the constraints mentioned above.

Finally, let’s try the same date as above but represented differently:

ticks('27/09/2021 13:44:35.0796736')

Again, it will return an error.

InvalidTemplate. Unable to process template language expressions in action 'Compose' inputs at line '1' and column '4715': 'In function 'ticks', the value provided for date time string '27/09/2021 13:44:35.0796736' was not valid. The datetime string must match ISO 8601 format.'.

The dates must be in ISO format; otherwise, Flow won’t be able to parse them.

Limitations

It’s not quite a limitation but something to keep in mind. Since exists thousands of ways to represent dates, Microsoft opted to support only one, the ISO 8601. It’s, in my opinion, a good move since it standardizes the way dates are provided and avoids a lot of problems in the parsing of dates. So the format is as year, month, and day separated by a hyphen and then hour, minute, second, and millisecond separated with a colon. For example:

2021-09-27T13:44:35.0796736Z

Please note that the “T” (separates the date from the time) and the “Z” (represents the UTC timezone) are necessary as well.

Recommendations:

Here are some things to keep in mind.

Use date objects

Date objects have the advantage of translating well to a valid string that the “ticks” function can parse. So, for example, the “utcNow” function returns a string with the current instant in time.

Don’t nest

There’s no real reason to do it, but if you find yourself in a situation where you have nested ticks functions in a formula, you should review it because you’re making a mistake 99% of the time.

Sources:

Microsoft’s ticks Function Reference

Back to the Power Automate Function Reference.

Photo by Nathan Dumlao on Unsplash