All geek questions in one place

Excel Power Query - convert date from UTC to local

I am connecting to an MS SQL database through Power Query (Version: 2.10.3598.81) in Excel 2010.

My dates are stored in UTC in the database, however I would like to show them as local in the spreadsheet.

DateTimeZone.From () - Converts to the DateTimeZone type, but assumes the input date is in the local time zone. This is the exact opposite of what I want.

DateTimeZone.ToLocal () - gives an error, I think, because there is no time zone information in the original date.

The local one in my case is the Australian EST, but it would be great if Excel just picked up the local time zone. (It seems to be done already)

+4
source share
1 answer

I think I found the answer.

The DateTime.AddZone () function, which I thought was used to convert between time zones, is actually used to add time zone information to an existing date. If your date is UTC, you should use DateTime.AddZone ([date_created], 0), and if your date was already in AEST, you would use DateTime.AddZone ([date_created], 10).

So now I can use:

DateTimeZone.ToLocal(DateTime.AddZone([date_created_UTC],0))

and Power Query will correctly convert my date created from UTC to AEST (or something else).

+3
source

Source: https://habr.com/ru/post/1534564/

More articles:


All Articles