Get a date in Power Automate, 3 expressions for filters and conditions
“How do I get a date 7 days before an expiration date using Power Automate?“
“Is there an expression similar to =TODAY() in Excel to get today’s date?“
Working dates can be challenging in Power Automate, not only with dates in SharePoint but also when you’re using multiple sources of the dates (Excel file, To Do task, external tool,…). Each of these tools can have date in a different format so simple comparison doesn’t do it, you need to format them in the same way.
There’re basically three functions/expressions you might need when using filters or conditions with dates.
utcNow()
The expression utcNow() is the equivalent to Excel’s TODAY() function. It provides today’s date for filter or condition, e.g. is expiration date today? But when used as it is, the function returns current date/time in ISO format, e.g. ‘2020-09-20T13:23:31.5443196Z’ = not really usable in filter nor condition. Therefore, as already mentioned, you need to change the date format by adding a parameter.
utcNow('<format>')The <format> definition is the same as used in the formatDateTime() function above. To get only date without time (year-month-day) it should be:
utcNow('yyyy-MM-dd')The exception when it’s not needed to use <format> is when the utcNow() expression is a part of another expression, e.g. addDays().
addDays()
The expression addDays() lets you add or subtract days from a date for time based filters/conditions, e.g. send a reminder 7 days before task due date or archive document 10 days after expiration date.
addDays(<date>,<number>)Although the expression is called addDays(), the actual operation depends on the <number> you enter. If it’s a positive number, it’ll add days to the <date>. If it’s negative number, it’ll subtract them.
Today + 7 days [is today 7 days before task due date?]
addDays(utcNow(),7)
Today - 10 days [is today 10 days after expiration date?]
addDays(utcNow(),-10)And as the other date/time operations in Power Automate, you have to format the dates using formatDateTime() expression again, before the actual condition or filter. As a result, the actual filter/condition might look quite complex as in the examples below.
Update: if you’d like to add a different time period than days, e.g. a month, there’s also addToTime(…) expression.
formatDateTime()
The expression fomatDateTime() enables you to format any date into a format of your decision. You don’t have to care about the original date format in the source system, you’ll define your own. The expression expects two parameters.
formatDateTime(<date>, '<format>')For example, if you want to compare dates without time (‘year-month-day’ only), you can use the <format> below.
formatDateTime(<date>, 'yyyy-MM-dd')The dates you are working with should always be formatted in the same way. More details on the formatting are described in my previous article.
Filter example: Get items with due date in 7 days
DueDate is a Date Only field, which makes the solution much easier as you need to convert only the output of the addDays() function.
DueDate eq 'formatDateTime(addDays(utcNow(),7),'yyyy-MM-dd')'
Condition example: IF today is 10 days after expiration date
ExpirationDate is Date & Time field, complicating the solution a bit as you need to convert both dates into the same format.
formatDateTime(items('Loop_all_items')?['ExpirationDate'],'yyyy-MM-dd') [is equal to] formatDateTime(addDays(utcNow(),-10),'yyyy-MM-dd')
A tip at the end: if your date condition is not working for any reason, it always helps to know what values enter the condition by storing them into two separate variables.