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

Power Automate filter on SharePoint Date and Time column

Posted on April 14, 2021April 14, 2021 by Tom

“Is there any way to set an ODATA Filter Query to let Power Automate filter all SharePoint items where some date field is less or equal to today’s date?”


Filter on SharePoint Date and Time column is one of the more complicated ones in Power Automate. Unlike many other SharePoint columns, it’s often not just Date eq ‘Value’. There’s a difference if your date contains time or not, you must use the right date format, and often also some calculation. With so many places for a potential error it might be challenging to build the correct Filter Query. Let’s take a look then on how to do it.

All the examples below are working with a SharePoint column DateTime (DateTime is also its internal name).

Filter out items with empty date

The first filter is to get only items where the date column has some value. If you process the dates later using the formatDateTime(…) expression, you’ll see an error message for empty dates. The template language function ‘formatDateTime’ expects its first parameter to be of type string. The provided value is of type ‘Null’. You can avoid it by filtering out the empty dates right away.

DateTime ne null
Power Automate date not equals null Filter query

Just type in the filter: your column’s internal name ne null. Don’t use null expression or single quotes, this is a special situation that doesn’t want it.

Filter ‘Date Only’ column

If your SharePoint column is ‘Date Only’, you should use date without time in the filter too. Since SharePoint will provide dates in ISO 8601 format, use the same format also for the ‘Value’. For date only column it means the format year-month-day: ‘yyyy-MM-dd’.

Don’t get confused by the date format in SharePoint, it’s displayed in the regional format but on the background it’s an ISO date.

SharePoint date format

You can use any operator on the ‘Date Only’ column: eq, ne, lt, le, gt, ge.

eq = equals
ne = not equal to
lt = less than
le = less than or equal to
gt = greater than
ge = greater than or equal to

For example, ‘Get items’ where date is in the past (less than today).

DateTime lt 'utcNow('yyyy-MM-dd')'
Power Automate filter date only column

Here you must already use the proper Filter Query format: an expression and single quotes around it.

Filter ‘Date & Time’ column

If your date column contains also time, you should consider how you want to work with it. You’ll always need to include some time in the ‘Value’ date too. For example, let’s use a similar filter as above: date is less than utcNow() (date & time of the flow run). With ‘Date & Time’ column it’ll include also items with date earlier today. If you run the flow at 11am, it’ll include all today’s items with time before 11am.

It’s not some problem you must solve, it’s just something to keep in mind.

DateTime lt 'utcNow()'
Power Automate filter date and time column

Note: utcNow() in this example will contain also time. utcNow(‘yyyy-MM-dd’) from previous example will take only date (at 00:00:00 time); you can use it to get start of a day for filters in a ‘Date & Time’ column.

Related to the time part is also the selection of operators. For ‘Date & Time’ column it doesn’t make sense to use the operators: eq, ne. SharePoint ‘Date & Time’ column goes down to seconds, it’ll probably be never equal and always not equal to your ‘Value’ date.

Summary

Having the right format of the filter is often only the first (but critical) step when filtering by a date. You could see three different situations in this article, three different filters:

  • filter empty date: no single quotes, no expressions
  • ‘Date Only’ column: single quotes, expressions, ISO format (‘yyyy-MM-dd’)
  • ‘Date & Time’ column: single quotes, expressions, ISO format, all dates have also time part

All the examples used only today’s date (& time) in all the filters, but you can use any date. You can add or subtract time period from a date to build reminders, even if the original date is in a calculated column.

You can even combine multiple filters to get items from a time period, e.g. a full day when working with date & time columns. And if your date filter is still not working, take a look on my old checklist of probable causes.


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.

4 thoughts on “Power Automate filter on SharePoint Date and Time column”

  1. Nestor Holynskyj says:
    November 26, 2021 at 8:17 pm

    I had high hopes that you’ve provided the solution to my frustrating problem. I have an identical need as your “Filter ‘Date Only’ Column”. My filter expression is “ArchiveTargetDate lt ‘utcNow(‘yyyy-MM-dd’)’ where ArchiveTargetDate is a date-only field in a SharePoint list. However, when I run it I get the following error: The expression “ArchiveTargetDate lt ‘utcNow(‘yyyy-MM-dd’)'” is not valid. Creating query failed.
    clientRequestId: 5c4f14e6-4479-48fc-a50e-7e56b8278870
    serviceRequestId: 5c4f14e6-4479-48fc-a50e-7e56b8278870

    This has been driving me crazy for days. Any advice would be appreciated!

    Reply
    1. Tom says:
      November 27, 2021 at 1:37 pm

      Hello Nestor,
      the whole utcNow(…) part must be entered as an expression in the Filter Query, not as a plain text.

      Reply
  2. RV says:
    March 21, 2022 at 6:06 am

    I have a similar error as Nestor when running the associated flow, where the response suggests the following is not valid as filter query:

    Last-Modified ge ‘@{subtractFromTime(utcNow(),5,’Minute’,’R’)}’

    I did enter the part within the ” as an expression. Is the expression too complex? It seems like the response is able to get a timestamp formatted the same as the Last-Modified column, so it’s as though it’s unable to compare the two dates. I assume there’s no way to have an expression on both sides of the operator?

    Reply
    1. Tom says:
      March 22, 2022 at 7:24 pm

      Hello RV,
      I think it doesn’t like the ‘R’ date format. When you work with dates from SharePoint you should always stick to the ISO format – there’s no need to format the date, you can keep it in the default format: subtractFromTime(utcNow(),5,’Minute’)
      And your assumption is correct, you can’t have an expression on both sides, the left side must be always the column as it is.

      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
Equip yourself with the tool to translate your thoughts into Power Automate expressions!

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.

  • Format results of date calculation in SharePoint columnJune 29, 2022
  • Why is your Power Automate flow creating duplicatesJune 26, 2022
  • How to create a unique identifier in your Power Automate flowJune 22, 2022
  • How to assign custom SharePoint permission level with Power AutomateJune 19, 2022
  • Remove permissions from a specific SharePoint user with Power AutomateJune 15, 2022

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

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