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:
Power Automate: Date Filtering in SharePoint Lists - Complete Guide with Examples
Let me share my notes on how to filter SharePoint Online list items using "Date and Time" columns, as I often need to re...
スポンサーリンク

Understanding DateTime Filters

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)

Expression

dateTime eq '@{utcNow()}' 
dateTime eq '@{variables('OctoberNineteenthMidnightUTC')}' // Sample source 
Result

Related Articles

Bonus: Why SharePoint Handles Timezone Conversions So Well

With the site timezone set to Japan:
We create this list:
When we change the timezone to UTC:
The time shifts back exactly 9 hours:

This demonstrates that SharePoint interprets the input time based on the site’s timezone settings at the moment of entry, and stores it accordingly.

Copied title and URL