Sep 16 2020 07:36 AM
I am using Power Query in Excel to Get Data from a SharePoint list. The Time in the Date/Time fields, in Excel, appears 4-5 hours later than what it is in the SP List. Why is this happening and how can I stop it from happening?
Sep 16 2020 11:34 AM - edited Sep 16 2020 11:34 AM
@IndyGal7 Out of the Box SharePoint lists display all times in the UTC time zone. To change that, for each site:
I hope this helps!
Belinda
Sep 17 2020 06:15 AM
Thanks for your response. We have confirmed all our time zones are correct. Something happens when we use Power Query to "Get Data", in the process the time zone changes with a 4 hr. difference. Whereas, when we export data from the SharePoint list, the time does NOT change.
Is it possible that Power Query filters it through a different server (GMT time)??
Appreciate help. @Ms_BelindaAllen
Sep 17 2020 06:53 AM
@IndyGal7 I'm not sure about about filtering through a different server, and it likely is something in the "get data" story.
If you can identify that the time zone being displayed is GMT or UTC, you can create a step to covert the time zone. If your field is Date and Time, as a single field. You Custom Column M function would be:
=DateTimeZone.ToLocal([dateTimeColumnName])
This would be the most efficient due to Daylight Savings time changes. Even if it means means making the time field a Date/Time, which would add today's date to the time, then using the field. Then you can reset the type back to time.
Sep 21 2020 06:51 AM
Thanks for your replay. I have 3 date time columns already so a work-around is not ideal, besides, i'd like to prevent other instances of this. @Ms_BelindaAllen
Sep 21 2020 07:26 AM
@IndyGal7 Sorry I couldn't help more. Perhaps an Excel person will chime in. Time zones can be very frustrating!
Nov 10 2020 03:02 AM
@Ms_BelindaAllen @IndyGal7 I have the same problem and I'm in discussion with Microsoft. All my time zone setting look correct but still my time zone setting are 9 hours out of sync. It is unbelievable that Microsoft promotes SharePoint lists to interact with PowerApps, Power BI Excel and Automate and that one of the components (SharePoint) has a wrong time zone and that can't be adjust.......
Working with data must have a correct time stamp. Always!! I hope that Microsoft gives me a good solution.
Unbelievable.