Drop down list & Time Zones.

[GEM]

Let's see if I get myself understood...

A1= cell to imput a time number, for example 11:00 AM
B1= Drop down validation, with Pacific Time, Mountain Time, Central Time,
Eastern Time.

Basically user enters time and selects which time zone this time represents.

A3=The time A1 represents in Pacific Time
B3=The time A1 represents in Mountain Time
C3=The time A1 represents in Central Time
D3=The time A1 represents in Eastern Time

How can I get A3, B3, C3, D3 to do this automatically, by letting the user
just enter a time number and its corresponding time zone??

 

[T. Valko]

How about something you probably didn't think of?

Daylight savings time!

I'm going to use abbreviations:

P = Pacific Time
M = Mountain Time
C = Central Time
E = Eastern Time

Create a table like this:

.........P...M...C...E
P.....0....1.....2...3
M...-1...0.....1...2
C....-2..-1....0...1
E....-3..-2...-1...0

Assume that table is in the range A8:E12

A1 = some time
B1 = some time zone selected from a drop down list

Enter this formula in A3 and copy across to D3:

=$A1+(VLOOKUP($B1,$A9:$E12,MATCH(A2,$A8:$E8,0),0)/24)+($A1+(VLOOKUP($B1,$A9:$E12,MATCH(A2,$A8:$E8,0),0)/24)<0)

Format as Time

 

[T. Valko]

Ooops!

I left out something very important!!!

A1 = some time
B1 = some time zone selected from a drop down list

A22 = P, M, C, E