Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

Get a date in Power Automate, 3 expressions for filters and conditions

Posted on September 20, 2020April 14, 2021 by Tom

“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.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

2 thoughts on “Get a date in Power Automate, 3 expressions for filters and conditions”

  1. Michael says:
    August 22, 2021 at 11:55 am

    Good day,
    I wrote a flow that should remind me of an expiry date 30 days to the expiry date. It should send me a reminder today but it didn’t. How can I share my flow with you?
    I actually used
    1) List
    2) Get future time
    3) Used substring to reduce the time format to just 10 characters to maintain only date format
    4) Used the get item option to select the SharePoint folder, the list and the column I want. I also set my query to (duedate eq get future time)
    5) Send an email section to send the mail to myself

    But I didn’t receive any notification today

    Reply
    1. Tom says:
      August 25, 2021 at 8:17 pm

      Hello Michael,
      take a look on the reminder article I wrote some time ago, I believe it’ll help.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Didn't find what you were looking for?
Need to adjust a solution to fit your needs?
Or would you just like to get an assistance from somebody with 1000s of hours of experience with Power Automate?

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes