Convert time from one time zone to local time.

Hello, I need some help. I have a attached a sample of what I need help with. I have a list of meetings located in different states. I live in AZ and I find myself looking up the time zone conversion quite often. I am looking for a way to automate the process.

I have attached a list with the meeting times in column B and the State in column E and county in column D if needed. I would like to automatically fill-in the AZ time equivalent which is in column C.


I found a good list of the states with their corresponding time zones. I have attached that as a sheet named "TimeZones".

My first thought was a V-lookup but I can't figure out how to make that work. I would prefer a macro that can do it all but anything that works and is efficient would be great. Thanks!

Here is my take on it.

First I converted all ranges to Excel tables since Excel tables copy things down automatically.

Most of the "heavy lifting" is done on the time zones sheet.

DST starts on the second Sunday in March and ends on the First Sunday in November. The range I7:M8 calculate these dates.

Cell J1 is the current date. Cell J2 determines if the current date is DST and Cell J3 is the offset to use in the VLOOKUP formula. It has a static name: My_Offset

Back on Sheet1, Enter your state in cell B1. Cell E1 gets the time difference between it and GMT.

Column F does the VLOOKUP for the local state, and column G is the difference between your state and the local state. These differences are applied to the local meeting time in column B to compute the time in your state in column C.