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 components).
For reference, see our previous article:
While date-only filters use the “2024-10-19” format, DateTime filters require the format “2024-10-19T00:00:00Z“.
The special characters “T” and “Z” have specific meanings:
T : Separator between date and time components
Z : Indicates UTC time standard (For Japan time, use “+09:00” instead of “Z”)
A key difference between date-only and DateTime filters is that DateTime filters can use the utcNow function directly (no need to add 9 hours).
This works because the timezone information (“Z” or “+09:00”) is included in the string, allowing SharePoint to automatically handle the timezone conversions appropriately.
※More details in the bonus section at the end.
For example, these two filter expressions are equivalent:
// Japan time: 10/19 00:00
dateTime eq '2024-10-19T00:00:00+09:00'
// UTC time: 10/18 15:00 = Japan time: 10/19 00:00
dateTime eq '2024-10-18T15:00:00Z'
Functions and Operators for Filter Queries
Let’s look at practical examples. As in our previous article, we’ll create filters using these operators: Operators
eq : equals
gt : greater than
ge : greater than or equal to
le : less than or equal to
lt : less than
and : AND operator
Practical Examples
We’ll be working with a SharePoint site configured with the following timezone settings:
We’ll apply filters to this list:
Since testing with the utcNow function in real-time can be challenging, we’ll use a timestamp string that simulates using the utcNow function at 00:00 JST on October 19 for our filter examples:
Retrieving Data Before Current Time
Expression
dateTime le '@{utcNow()}'
dateTime le '@{variables('OctoberNineteenthMidnight')}' // Sample source
// Use 'lt' to exclude current time
Result
Retrieving Data After Current Time
Expression
dateTime gt '@{utcNow()}'
dateTime gt '@{variables('OctoberNineteenthMidnight')}' // Sample source
// Use 'ge' to include current time
Result
Retrieving Data Between 12 Hours Ago and Current Time
Expression
dateTime ge '@{addHours(utcNow(), -12)}' and dateTime le '@{utcNow()}'
dateTime ge '@{addHours(variables('OctoberNineteenthMidnight'), -12)}' and dateTime le '@{variables('OctoberNineteenthMidnight')}' // Sample source
// Use 'gt' to exclude 12 hours ago, 'lt' to exclude current time
Result
Retrieving Data Between Current Time and Next 24 Hours
Expression
dateTime gt '@{utcNow()}' and dateTime lt '@{addHours(utcNow(), 12)}'
dateTime gt '@{variables('OctoberNineteenthMidnight')}' and dateTime lt '@{addHours(variables('OctoberNineteenthMidnight'), 12)}' // Sample source
Result
Retrieving Data Exactly Matching Current Time (Rarely Used)