Skip to content

Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Problems
  • Triggers
  • Application specific solutions
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Resources
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 *

NOW AVAILABLE:

The Ultimate Power Automate expressions cheat sheet
Spend your time thinking about what the flow should do, not how to do it!

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.

Hello and welcome!

My name is Tom and I'm a business process automation consultant and Microsoft MVP living in the Czech Republic. I’ve been working with Microsoft technologies for almost 10 years, currently using mainly Power Automate, SharePoint, Teams, and the other M365 tools.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by "clicking" the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that's what this blog is about.

To make the step from no-code Power Automate flows to low-code flows: using basic coding knowledge to build more complex yet more efficient flows to automate more of your daily tasks.

  • How to create a new SharePoint list column with Power Automate flowMay 22, 2022
  • How to combine expressions in your Power Automate flowsMay 18, 2022
  • Import Planner tasks with checklists into various buckets (Power Automate)May 15, 2022
  • How to get notified when Planner task was reassigned (Power Automate)May 11, 2022
  • How to extract value from XML using Power Automate flowMay 8, 2022

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

© 2022 Let's POWER Automate | Powered by Superbs Personal Blog theme