Power Query in Excel - Get Data - changes Time by 4 hrs.

[IndyGal7]

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?

[#2]

@IndyGal7 Out of the Box SharePoint lists display all times in the UTC time zone.  To change that, for each site:

1. Click on the Settings Cog/icon in the top right corner.
2. Select Site Information
3. Select View Site Settings
4. In the Site Administration area select Regional Settings
5. Change the Time zone.

I hope this helps!

Belinda

[#3]

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 

[#4]

@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.

[#5]

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 

[#6]

@IndyGal7 Sorry I couldn't help more.  Perhaps an Excel person will chime in.  Time zones can be very frustrating!

[#7]

@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.