Let me share my notes on how to filter SharePoint Online list items using “Date and Time” columns, as I often need to reference these patterns.
Functions and Operators for Filter Queries
Here are the essential functions and operators you’ll combine to create effective date filters:
Functions
- formatDateTime function : Converts timestamps into formatted date strings
- addHours(utcNow(), [n]) : Combines two functions to get current time. Replace [n] with your UTC offset (e.g., +9 for Japan, -5 for EST, +1 for CET)
- addDays function : Used for date calculations (e.g., n days before/after)
Comparison Operators for Filter Expressions
- eq : equals
- gt : greater than
- ge : greater than or equal to
- le : less than or equal to
- lt : less than
Additional Filter Operators
- and : AND operator
- or : OR operator (less commonly used in date filtering)
Practical Examples
Let’s look at some practical examples. These tests were conducted on October 19, 2024, using the following sample data:
We’ll apply these filter expressions in the “Get Items” action and verify the results using a CSV action:
Retrieving Today’s Data
Expression
date eq '@{formatDateTime(addHours(utcNow(), 9), 'yyyy-MM-dd')}'
Result
Retrieving Data from Exactly One Week Ago
Expression
date eq '@{formatDateTime(addDays(addHours(utcNow(), 9), -7), 'yyyy-MM-dd')}'
Result
Retrieving Data for Exactly Three Days Later
Expression
date eq '@{formatDateTime(addDays(addHours(utcNow(), 9), 3), 'yyyy-MM-dd')}'
Result
Retrieving Data from More Than a Week Ago
Expression
date le '@{formatDateTime(addDays(addHours(utcNow(), 9), -7), 'yyyy-MM-dd')}' // Change to 'lt' for dates strictly before one week ago
Result
Retrieving Data After Three Days from Now
Expression
date gt '@{formatDateTime(addDays(addHours(utcNow(), 9), 3), 'yyyy-MM-dd')}' // Change to 'ge' for dates including three days later
Result
Retrieving Data from One Week 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
Retrieving Data Between Tomorrow and One Week 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, please refer to this article:
![]()
Power Automate: Advanced DateTime Filtering in SharePoint Lists - Complete Guide
This article continues from our previous guide about date-only filtering, focusing on DateTime columns (including time c...
ippu-biz.com
2025.02.12