August 3, 2024

# 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

#### Manuel Gomes

I have 18 years of experience in automation, project management, and development. In addition to that, I have been writing for this website for over 3 years now, providing readers with valuable insights and information. I hope my expertise allows me to create compelling, informative content that resonates with the audience.

View all posts by Manuel Gomes →

## 3 thoughts on “Power Automate: ticks Function”

1. JVFLA says:

The ticks function is quite easy to understand. However, converting from ticks back to an ISO 8601 date/time string is not so straightforward. I’m still looking for a solution that works well.

For example, you have a date/time and you want to add ONE tick.

2. Aedu says:

When I convert 44466 in Excel back to Long Time format the time is lost.

3. Mark says:

I don’t really get your example: