Power Automate OData Filter Query Cheat Sheet: SharePoint Date & Time

This cheat sheet serves as a quick reference for filtering SharePoint Online list items using “Date and Time” columns in Power Automate. I created these patterns to easily copy and paste into the “Filter Query” field of the Get items action.

Essential Functions and Operators
Practical Examples
Filters Including Both Date and Time

Essential Functions and Operators

Combine these functions and operators to build effective date filters. Note: The examples below use +9 for Japan Standard Time (JST). Please replace 9 with your own time zone offset (e.g., -5 for EST).

Key Functions

formatDateTime: Converts timestamps into formatted date strings (e.g., ‘yyyy-MM-dd’).
addHours(utcNow(), [n]): Adjusts the current UTC time to your local time zone. Replace [n] with your offset.
addDays: Adds or subtracts days from a specific date (e.g., -7 for 7 days ago).

Comparison Operators

eq: Equals
gt: Greater than
ge: Greater than or equal to
le: Less than or equal to
lt: Less than

Logical Operators

and: Returns items only if BOTH conditions are true.
or: Returns items if EITHER condition is true.

Practical Examples

The following examples demonstrate how to filter items based on relative dates.

Verification Context:
These tests were conducted on October 19, 2024, using the sample data below:

We applied these filter expressions in the Get items action and verified the results using a CSV table:

1. Filter: Today’s Items

Expression

date eq '@{formatDateTime(addHours(utcNow(), 9), 'yyyy-MM-dd')}'

Result

2. Filter: Exactly 7 Days Ago

Expression

date eq '@{formatDateTime(addDays(addHours(utcNow(), 9), -7), 'yyyy-MM-dd')}'

Result

3. Filter: Exactly 3 Days Later

Expression

date eq '@{formatDateTime(addDays(addHours(utcNow(), 9), 3), 'yyyy-MM-dd')}'

Result

4. Filter: Older Than 7 Days

Expression

date le '@{formatDateTime(addDays(addHours(utcNow(), 9), -7), 'yyyy-MM-dd')}' 
// Use 'lt' if you want to exclude the 7th day itself

Result

5. Filter: Future Items (After 3 Days)

Expression

date gt '@{formatDateTime(addDays(addHours(utcNow(), 9), 3), 'yyyy-MM-dd')}' 
// Use 'ge' to include the date exactly 3 days from now

Result

6. Filter: From 7 Days Ago Until Today

Expression

date ge '@{formatDateTime(addDays(addHours(utcNow(), 9), -7), 'yyyy-MM-dd')}' and date le '@{formatDateTime(addHours(utcNow(), 9), 'yyyy-MM-dd')}'

Result

7. Filter: Next Week (Tomorrow to 7 Days Later)

Expression

date gt '@{formatDateTime(addHours(utcNow(), 9), 'yyyy-MM-dd')}' and date lt '@{formatDateTime(addDays(addHours(utcNow(), 9), 7), 'yyyy-MM-dd')}'

Result

Filters Including Both Date and Time

For examples of filters that consider both date and time components (e.g., specific hours), please refer to the following guide:

How to Filter by DateTime in Power Automate