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

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.

6 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
  3. Toby says:
    September 16, 2022 at 3:00 pm

    This reminds me of my favourite bug ever. I’d written some VBA that utilsed the current date. It seemed to work about one in 5 times but throw an error the rest of the time.
    Turns out I had used a date format string of dd-mm-yyyy – should have been dd-MM-yyyy. Which meant that if the code ran when the current time was between hh:01 and hh:12, the code would complete (but only with a 1 in 12 chance of giving the right month). When the minutes were between hh:13 and hh:00, the code would crash with a date format exception.

    (PS I haven’t written VBA in 2 decades so it may be that the working minutes were 0-11 – don’t shoot me!

    Reply
    1. Tom says:
      September 27, 2022 at 3:30 pm

      Hello Toby,
      that’s a good one, I’ve seen it a few times already and it’s always so much fun debugging before you find it… 🙂

      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.

  • How to find Excel rows missing in SharePoint list (Power Automate)March 29, 2023
  • Check whether user exists in AAD with Power AutomateMarch 26, 2023
  • How to highlight whole SharePoint row based on calculated dateMarch 22, 2023
  • Why is your Power Automate flow running so slow?March 19, 2023
  • How to add multiple links to the Power Automate approval taskMarch 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes