Portrait of Nava bitOfNava A digital notebook on Power Platform development, debugging, and scalable app design.

April 12, 2026

Filter SharePoint date field in Power Automate

Solving the SharePoint/Power Automate "Date Gap

The Problem: The UTC Ghost

When querying SharePoint via Power Automate, records appear to be "missing" or "offset" by a day. This happens because SharePoint stores all data in UTC, while the user interface and local flows operate in a local offset (e.g., UTC+3).

A visitor arriving at 1:00 AM on April 12th (Local) is saved as 10:00 PM on April 11th (UTC). A standard OData filter for "Today" starts at midnight UTC, effectively missing that visitor or shifting them to the wrong day.

The Failed Attempts (Why they didn't work)

  • Hardcoded UTC Strings: * Result: Failed. It created a static window that didn't account for the daily roll-over or the 3-hour local lead time.

  • Calculated Columns (=TEXT([Date], "yyyy-mm-dd")): * Result: Failed. SharePoint’s OData API (Get Items) does not support filtering on Calculated column types. It throws a 400 Bad Request.

  • Direct startOfDay(utcNow()) Filters: * Result: Failed. Because of the UTC+3 offset, this filter either missed the first 3 hours of the business day or included the last 3 hours of the previous day.

The Solution: The "DateKey" Architecture

To bypass timezone math and OData limitations, we shifted from a dynamic date query to a static string comparison.

1. SharePoint Schema Change

Create a column named DateKey set to Single Line of Text (Not Date).

Why? Text columns are fully indexable and filterable in OData. They do not shift based on the viewer’s timezone like date/datetime.

2. The App-Side Logic (The "Write once" Rule)

The change should not be manual. Whenever a record is saved via Power Apps, the DateKey must be generated at the point of entry.

Formula (Power Apps):

Patch(YourList, Defaults(YourList), {
    Entrydate: Now(),
    DateKey: Text(Now(), "yyyy-mm-dd")
})

Result: This "locks" the date as a string (e.g., "2026-04-12") based on the user's local context at the moment of submission.

3. The "Infinite Loop" Safeguard (Automated Flow)

If using a flow to update the DateKey on existing items or modifications, a Trigger Condition is mandatory to prevent a self-triggering loop:

Condition: @not(equals(triggerOutputs()?['body/DateKey'], formatDateTime(triggerOutputs()?['body/Entrydate'], 'yyyy-MM-dd')))

4. The Final "Bulletproof" Filter

In the Daily Alert flow, the filter query is now simplified to a string match:

Filter Query: DateKey eq 'replace_with_expression'

Expression: convertFromUtc(utcNow(), 'E. Africa Standard Time', 'yyyy-MM-dd')

Summary for Future Me

  • Never filter on a DateTime column if you only care about the Date.
  • Always normalize the date into a yyyy-MM-dd string column (DateKey) at the source (App or Flow).
  • Avoid Calculated Columns for filtering; use a standard Text column updated via logic. PowerApps filters can't use Calculate columns for ODATA filtering

Questions I Leave for My Future Self

  • Should I store dates in local timezone or UTC in the future?
  • Can DateKey be created via the app or PowerAutomate is the best way?
  • Can I convert localtime to UTC or vice-versa and do the filter at runtime instead of creating an additional column?
  • What is the speed difference when using text vs date data type in fetching records?

Outcome

This kind of cleanup usually does not require a major redesign. In many cases, the improvement comes from removing repeated work and making the screen state more predictable.

That makes the app faster and the next debugging session easier.