Converting Time Zones

TimeZone conversions can be tricky, especially when DST applies. Unfortunately, there is not a good way to directly convert a DateTime to another zone (factoring in DST). However, there are free Rest APIs you can use that don't require an API key (and more free ones that do).  Here are two examples.

If you only need to convert a time on today's date, you can simply get the offset value with this expression. You can remove the [utc_offset] part to see what other info is there. You can then use another function to add hours or switchzone by that number of hours (after you convert it to a number).

= Json.Document(Web.Contents("http://worldtimeapi.org/api/timezone/Africa/Cairo"))[utc_offset]

If you have a column of past/future datetimes and need to convert them (accounting for DST), you can use this function I wrote that uses a different REST API.

//fnConvertTimeZone

(dt as text, fromZone as text, toZone as text) =>
let
BaseURL = "https://www.timeapi.io/api/Conversion/ConvertTimeZone",
headers = [#"Content-Type" = "application/json"],
Body = [dateTime = dt, fromTimeZone = fromZone, toTimeZone = toZone, dstAmbiguity=""],
Response = Web.Contents(BaseURL, [Headers = headers, Content = Json.FromValue(Body)]),
Result = Json.Document(Response)[conversionResult][dateTime]
in
Result

You can then Invoke Custom Column passing in your DateTime and zone info like this. I named the function fnConvertTimeZone. There is likely a limit on this API, so it may not work with too many rows.

= fnConvertTimeZone("2021-03-14 17:45:00", "Europe/Dublin", "Africa/Cairo")

or with a column reference (with the text formatted as shown above)

= fnConvertTimeZone([DateTime Column], "Europe/Dublin", "Africa/Cairo")

Pat