Convert UTC to Local Time by Location

[Mychele]

I've checked multiple posts in the community, but can't quite figure out how to do what I need.  I have orders from multiple locations, across multiple time zones, and some of the locations don't participate in Daylight Saving Time.  Yet, somehow, I need to convert the order time from UTC to local time.  Parsing out the hour from the timestamp portion is easy, I just don't know to build in all the other logic and have it work during DST and without DST.  

Order #	Location	Order Date	Order Time (UTC)	Time Zone	Daylight Saving	UTC to Local	Convert to DST	Expected Result
10	1	7/15/2020	2020-07-15T15:00:00	Central	Y	UTC minus 6	N/A in July	9
11	2	7/15/2020	2020-07-15T19:00:00	Central	N	UTC minus 6	N/A - No DST at this location	13
12	3	7/15/2020	2020-07-15T20:00:00	Eastern	Y	UTC minus 5	N/A in July	15
90	1	12/15/2020	2020-12-15T15:00:00	Central	Y	UTC minus 6	UTC minus 5	10
91	2	12/15/2020	2020-12-15T19:00:00	Central	N	UTC minus 6	N/A - No DST at this location	13
92	3	12/15/2020	2020-12-15T20:00:00	Eastern	Y	UTC minus 5	UTC minus 4	16

This site has a small table of Daylight Saving Time for the next few years, which might help when evaluating an order date to see if it's in DST or not.  While a dynamic solution is ideal, I'm not opposed to hard coding this table as a Text Input, since I hope my project doesn't need to exist more than a few years.

https://www.timeanddate.com/time/change/@5366730

Sample data is attached.  

Thanks so much for reading and please tell your dog I said hi!

--Mychele

[cmcclellan · Accepted solution]

I didn't include the DST calculation, but this is what I did : 

[Mychele]

This is great!  Thanks!

[Mychele · Accepted solution]

For reference, here's the final solution.