Power Automate filter on SharePoint Date and Time column

“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

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.

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')'

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()'

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.

First Name

Email address

Zero spam, unsubscribe anytime.

Leave this field empty if you’re human: