Forum Discussion
Power Query in Excel - Get Data - changes Time by 4 hrs.
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?
6 Replies
IndyGal7 Out of the Box SharePoint lists display all times in the UTC time zone. To change that, for each site:
- Click on the Settings Cog/icon in the top right corner.
- Select Site Information
- Select View Site Settings
- In the Site Administration area select Regional Settings
- Change the Time zone.
I hope this helps!
Belinda
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
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.
