Calculate different time zone times only knowing what time zone you are in.

[donnamkelley]

I have the following information:
[TABLE="width: 205"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Depart Time[/TD]
[TD]Time Zone[/TD]
[TD]My Time[/TD]
[TD]My Time Zone[/TD]
[/TR]
[TR]
[TD="align: right"]7:00 AM[/TD]
[TD]ET[/TD]
[TD="align: center"]FALSE[/TD]
[TD]CT[/TD]
[/TR]
[TR]
[TD="align: right"]8:00 AM[/TD]
[TD]ET[/TD]
[TD][/TD]
[TD]MT[/TD]
[/TR]
[TR]
[TD="align: right"]9:15 AM[/TD]
[TD]ET[/TD]
[TD][/TD]
[TD]PT[/TD]
[/TR]
</tbody>[/TABLE]
How do I get Excel to tell what time it is in my time zone? I will need this to work east to west and west to east. I appreciate any help offered.

 

[Eric W]

Welcome to the board.

	A	B	C	D	E	F
Depart Time	Time Zone	My Time	My Time Zone	ET
ET	CT	CT
ET	MT	MT
ET	PT	PT
MT	CT
PT	ET

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]7:00 AM[/TD]

[TD="align: right"]6:00 AM[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]8:00 AM[/TD]

[TD="align: right"]6:00 AM[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]9:15 AM[/TD]

[TD="align: right"]6:15 AM[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1:00 PM[/TD]

[TD="align: right"]2:00 PM[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2:30 PM[/TD]

[TD="align: right"]5:30 PM[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=A2+(MATCH(B2,$F$1:$F$4,0)-MATCH(D2,$F$1:$F$4,0))/24[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put a list of the time zones in order in F1:F4. Then the formula in C2 should work for you. You may have issues with times near midnight, but that's fixable if you want to include a date with the time.

Let me know how this works.

 

[donnamkelley]

Works perfectly, Eric. Thank you so much!

 

[Eric W]

Glad to help!