US Timezone Coverter

[Dmix2]

Hello all,

I am trying to figure out what the formula would need to be in order to make this work, see attached. In cells A1:A4 I have a list of US timezones. In cells C5 and F5 I have made dropdown menus to select the time zones. In cell D5 and G5, I have formatted this to be military time automatically when entering plain numbers (00\:00). So here's the intent: I would like to be able to select a timezone in cell C5, type in a time in D5, then select what timezone i would like it converted to in cell F5, and have this all autocalculated in cell G5. Any help is much appreciated!

[queuesef]

I made 4 lookup tables (one for each zone)....series of If formulas to ADD number or hours (in some cases it is SUBTRACTED as noted by NEGATIVE amounts! In Column G is the number of Conversion Hours which is added to the Time (needed to divide by 24 hours to add to TIME)...
See attachment. Good luck...I'm sure there are better ways....here's just a quickie! (VBA may be better choice...explore!)

[Dmix2]

Brilliant! Thank you!