Time Zone adjustment formula

Question

I have a list of US cities in Col F. Col H states their time zone as "central", "mountain", "Eastern", "pacific".

I have a time (EST) in $H$1 in 12-hr format, is there an easy way to convert that time to central, mountain, and pacific in Col I based on the time zone value in col H?

Thanks

Hile

Answer 1

Try this formula on row 2 in the column where you want the local times:

=$H$1+VLOOKUP(UPPER(H2),{"EASTERN",0;"CENTRAL",-1;"MOUNTAIN",-2;"PACIFIC",-3},2,FALSE)/24

The numbers 0, -1, -2, and -3 are the differences of the respective time zone compared to "Eastern time".

Format the cell with the time format you want to have.

Copy down as far as you have data in columns F and H.

Hope this helps / Lars-Åke

Answer 2

Perfect! Thank you.