Date Time Expression cheat sheet

Exactly what the title says. A list of expressions for Date and Time in Power Automate which I can use as a cheat sheet. This is a note to self kind of article.

I will keep updating this article over time.

Last updated: November 06, 2024

Conversions

A couple of expressions I used for time conversion in the past.

Julian Date Number to UTC
Something with 4713 BC. I found the calculation for seconds in the Julian Day Wikipedia page and a time conversion tool at Nasa. This sample uses a variable called JDN. An example value would be 2459284.125.

Ticks to UTC
The number of 100 nanosecond intervals since 1 January 1601 00:00:00 UT. Ed Gonzales has a great article about this called Moving Targets. Microsoft also has a Time interval table with all the ticks values.

In this example you can use 637516029749073500 as a value for the Ticks variable.

UTC to Ticks in Minutes

UTC to Ticks in Days

UTC to Ticks in Non-Leap Years

Points in time

A couple of calculations to determine a certain point in time.

First day of next Month
Power Automate has addToTime and startOfMonth functions which you can use for this. This sample uses a the utcNow function for the datetime stamp.

Get first Tuesday of the Month
Power Automate has dayofweek and dayofmonth. But it doesn’t have firstdayofmonth. This sample uses a variable called Now. An example value would be the UTCNow function.

Most recent day
Comparing two date values can be done with conditions but sometimes challenging. Ticks can make your life easier. This sample is comparing two dates (tomorrow and now). Converting them into an array with ticks in days values and extracting the maximum value of it (most recent day).