Time zone conversion (please help)

[Coryanthony]

Hello, I am looking to convert Central Time zone to Jamaica (GMT-5) time zone. 

Jamaica time does not observe day light savings. During winter, it is Eastern Time zone, during summer it is Central time zone.

Please help.

[Greg_Deckler]

@Coryanthony OK, so a different take on this. You could do this in DAX. To add/subtract hours in DAX goes like this:

[DateTime Column] + ( 5 * 1/24).

That will add 5 hours for example. Note that this works because Dates and Times in DAX are a decimal number where the integer portion is the number of days since 12/30/1899 and the decimal portion is fractions of a day.

OK, on to the bigger issue, when to apply the +/- different amoutn of hours. The most exact way would be to have a table that stored exactly when daylight savings time began and ended. 

Year, DST Start, DST End

2023, 3/12/2023, 11/5/2023

If you just need it to be "close" a more automated method might be to use my equinox calculations here:

Equinoxes and Solstices - Microsoft Power BI Community

[Bmejia]

Add a custom Column, Where 5 is the difference in Hours
[Time]+#duration(0,5,0,0)

Solved: How to add hours to DateTime data? - Microsoft Power BI Community

[Coryanthony]

@Bmejia 

will this account for day light savings? Data is currently in Central time zone, while Jamaica is 1+ hr ahead during winter and same time during summer.

Thank you for the response.

[Bmejia]

I don't believe it will, cause you adding the hour or time manually . The only way that it would probably work is if you had a date and time, you could use this, then extract the time.

0 is the time zone you want to changed to.
=DateTime.AddZone([MyDateColumn],0)

[Coryanthony]

Thank you @Bmejia 

i can go back a few steps. So if i have date and time together, i can create a new column using =DateTime.AddZone([MyDateColumn],0) ?

[Bmejia]

Yes, so create a new custom column in power query enter the code below, change the value zero to whatever time zone you wanted to be. Then extract the time.

[Coryanthony]

will this account for day light savings? Data is currently in Central time zone, while Jamaica is 1+ hr ahead during winter and same time during summer.

Thank you for the response.

[thx]

I have a similar question.

Any one knows of a possible solution? Help!!!

[Idrissshatila]

Hello @Coryanthony 

check this link https://youtu.be/eH9dHfNG8kg

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

Follow me on Linkedin

[Coryanthony]

Thank you @Idrissshatila ,

I watched the video and subscribed but was still unable to figure it out.