UTC to AEST

If you want to convert dates and times between timezones, you must create an Excel file for each of those timezones.

Before each run, you must adjust the timezone on you local computer. And close and reopen the Excel file, if I remember correctly.

FYI it took me about 3 hours for all approx. 130 timezones, so let's say 1-2 minutes per timezone.

Next you need to combine the files in a separate Excel sheet, like this:

This is just a simple combine of the individual files, with the file names as timezones.

(OK, I added semicolons but that's not really necessary).

Finaly you need a function for the actual conversion. This function will first calculate the UTC datetime from the source datetime and next calculate the datetime in the destination zone from the UTC datetime. Both source and destination time zones must be the names of the zones in the combined table.

I can share the code with my file- and tablename.

Unfortunately it is all in Dutch (except for the function name), but it should still work (after you adjust the source to your source).

Note: the code was written back in December 2016 and it may not be very efficient (I'm not sure). At some time in the (near?) future I will create new code in English, probably more efficient by creating a large calendar table for each quarter of an hour, so data can be merged directly.

For now, this is the code I can share:

let
    fnDateTimeBetweenZones = (ZoneFrom as text, DateTimeFrom as datetime, ZoneTo as text) as datetime =>
let
//    ZoneFrom = "(UTC+10:30) Lord Howe Island",
//    DateTimeFrom = DateTime.FixedLocalNow(),
//    ZoneTo = "(UTC-09:00) Alaska",
    Bron = Excel.Workbook(File.Contents("C:\Users\Marcel\OneDrive - Bemint\Office 365\Power Query\DateTimeTables\Windows Time and Dates.xlsx"), null, true),
    GlobalTimeTable_Table = Bron{[Item="GlobalTimeTable",Kind="Table"]}[Data],
    #"Type gewijzigd" = Table.TransformColumnTypes(GlobalTimeTable_Table,{{"Tijdzone", type text}, {"UTC", type datetime}, {"Lokaal", type datetime}}),
    #"Gefilterde rijen" = Table.SelectRows(#"Type gewijzigd", each ([Tijdzone] = ZoneFrom)),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Gefilterde rijen", "DateTimeFrom", each DateTimeFrom),
    #"Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Aangepaste kolom toegevoegd", "Lokaal<=DateTimeFrom", each [Lokaal]<=[DateTimeFrom]),
    #"Gefilterde rijen1" = Table.SelectRows(#"Aangepaste kolom toegevoegd1", each ([#"Lokaal<=DateTimeFrom"] = true)),
    #"Laatste rijen behouden" = Table.LastN(#"Gefilterde rijen1", 1),
    #"Aangepaste kolom toegevoegd2" = Table.AddColumn(#"Laatste rijen behouden", "UTCDatumTijd", each [DateTimeFrom]+([UTC]-[Lokaal])),

/* Nu de UTC-tijd omrekenen naar lokale tijd in ZoneTo */
    #"Gefilterde rijen2" = Table.SelectRows(#"Type gewijzigd", each ([Tijdzone] = ZoneTo)),
    #"Aangepaste kolom toegevoegd3" = Table.AddColumn(#"Gefilterde rijen2", "UTCDatumTijd", each #"Aangepaste kolom toegevoegd2"[UTCDatumTijd]{0}),
    #"Aangepaste kolom toegevoegd4" = Table.AddColumn(#"Aangepaste kolom toegevoegd3", "UTC<=UTCDatumTijd", each [UTC]<=[UTCDatumTijd]),
    #"Gefilterde rijen3" = Table.SelectRows(#"Aangepaste kolom toegevoegd4", each ([#"UTC<=UTCDatumTijd"] = true)),
    #"Laatste rijen behouden1" = Table.LastN(#"Gefilterde rijen3", 1),
    #"Aangepaste kolom toegevoegd5" = Table.AddColumn(#"Laatste rijen behouden1", "DateTimeTo", each [UTCDatumTijd]+([Lokaal]-[UTC])),
    DateTimeTo = #"Aangepaste kolom toegevoegd5"[DateTimeTo]{0}
in
    DateTimeTo
in
    fnDateTimeBetweenZones