Filter sharepoint list using DateOnly field, should we pass the local date or UTC date

(0) Share

Report

Posted on by johnjohnPter

2,015 Season of Giving Solutions 2025

now i a have a dateonly inside sharepoint i enter it as 15/05/2026 and it got saved as 14/05/2026 21:00 inside sharepoint, as the local setting and sharepoint site setting is UTC+3.. then inside power apps i added a Date Picker , and i set this filter to the gallery:-

Filter(SPlist, DateOnlyField = DatePicker.SelectedDate)

where i selected 15/05/2026 inside the date picker and i got the above item, although in shrepoint it is stored as 14/05 and not 15/05... so where was the magic?

I have the same question (0)

All responses (8)

Answers (0)

Sort by

Suggested answer

WarrenBelz 155,840 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!
@johnjohnPter

There is no actual "magic" - a lot depends on the time zone of the SharePoint site and the local user device. If they are the same, there is no issue.

What actually happens in the background is that the SharePoint database saves the actual UTC value of the date/time entered into the user's device. If the SharePoint site has a different Regional Setting, then viewing the data in a SharePoint View converts it to the Time Zone of the SharePoint site. Viewing it (or querying it) from Power Apps looks for the conversion to the time zone of the local device.

Date only fields are converted to time 0:00 on the same date, so if the SharePoint site is behind the local machine, this will be sometime on the previous day.

Example -

Local device UTC + 10 and SP Site UTC + 8

User saves a date 15/5/2026 0:00 in Power Apps

SP Database converts and stores to UTC 14/5/2026 14:00 (10 hours behind)

When displayed on SP Site View at + 8, converts to 14/5/2026 22:00 ( UTC + 8 - so you will see the day behind there)

When viewed in Power Apps at UTC + 10, comes back to 15/5/2026 0:00 (so you will see the correct day)

I have this exact issue with a customer I have in Western Australia (UTC + 8) as I am in Queensland (UTC + 10)

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
johnjohnPter 2,015 Season of Giving Solutions 2025 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz thanks for the great helpful reply, but i am still confused, in my case the sharepoint site and local users' machine are the same UTC+3. so i am not sure how filtering the gallery using 15/05/2026 00:00 will get an item that is stored as 14/05/2026 21:00 ???

Was this reply helpful? Yes No
WarrenBelz 155,840 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@johnjohnPter,

How are you looking at the data in SharePoint - the "back end" data storage is absolutely correct for UTC + 3 - as the UTC time conversion for the date stored is minus 3 hours. If you are looking at it from a Flow, then yes, you will see the "raw" UTC time and need to do a Time Zone Conversation element to get it back to what you need, however a SharePoint View should show the conversion based on the Regional Settings of the SharePoint site (one of mine below) - as should Power Apps for the user's device settings (which it is)

So you might want to check this first.

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
johnjohnPter 2,015 Season of Giving Solutions 2025 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz thanks again for the reply. i know that sharepoint list respect the site settings and power apps respect the user local settings... when i say that the value is atored as 14/05/2026 21:00 i am talking about how it is stored internally inside sharepoint and retrived using row api call _api/getlistbytitme/items(1)...

Was this reply helpful? Yes No
WarrenBelz 155,840 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@johnjohnPter,

That is absolutely correct - the API call will retreive the "raw" UTC date/time from the SharePoint data storage, so what you are seeing is expected. If you really want to fix this, use AddHours in a Compose action after your API call in the Flow (example)
addHours(outputs('Send_an_HTTP_request_to_SharePoint')?['body'],3)

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
johnjohnPter 2,015 Season of Giving Solutions 2025 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz but back to my original question, how a value that is stored inside sharepoint as 14/05/2026 21:00 can be retrieved inside power apps using this formula:-

Filter(SPlist, DateOnlyField = DatePicker.SelectedDate) // where selected date from the DarePicker is 15/05/2026?

Thanks

Was this reply helpful? Yes No
WarrenBelz 155,840 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@johnjohnPter,

Very simply - Power Apps uses the Regional setting of the local device when displaying the "raw" UTC date/time stored in SharePoint. So if the device is set at UTC + 3, Power Apps adds three hours to the SharePoint stored date/time (and gets it back to what it was set when it was stored). This is the reverse of when Power Apps is writing to the SharePoint data storage which is always at UTC, so if Power Apps is at UTC + 3, SharePoint takes off 3 hours, then stores it.

So in simple terms:

Date picker (or however user sets it) selects 15/5/2026 (actually 2026-05-15 00:00) with device at UTC + 3.

Saves to SharePoint as date/time UTC conversion 14/5/2025 21:00 ( 2026-05-14 21:00 ). This is what you see in the SharePoint backend storage and have to work with outside Power Apps or a SharePoint View.

You then query SharePoint from Power Apps using Filter(SPlist, DateOnlyField = DatePicker.SelectedDate).

Power Apps then adjusts this to UTC (so 2026-05-15 00:00 becomes 2026-05-14 21:00 ) and sends it to SharePoint, which then compares this and you get a match.

As I mentioned earlier, if the SP site and Power Apps are the same Time Zone, it all works perfectly and you will not encounter any issues unless you are using dates in Power Automate (then all you need to do is convert it as I posted)

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
WarrenBelz 155,840 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@johnjohnPter,

A quick follow-up to see if you received the answer you were looking for. Happy to assist further if not.

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No