Automatically show time in different time zones.

[DPChristman]

I do a lot of work coast to coast, and much of it involves reviewing CCTV footage.

I am looking for a formula that can pull up the local time in another time zone (maybe by zip code).

Is there an easy formula for this that doesn't involve complicated macros.

I can always just do a Google search when I need to look one up, but I am trying to find a simpler was of accomplishing this in a list of locations.

 

[MARK858]

Assuming you are in the States, possibly something like the below (yellow highlighted cell is the time entry cell based on UTC, Column B is the State to look up), please note I am in the UK so might be off the mark on what you are looking for here.

Book1
	A	B	C	D	E	F	G	H	I	J
1	UTC time	State	Result		State / federal district or territory	Time Offsets	Plus or minus		DST	Details
2	09:00	Arkansas	03:00:00		Alabama	06:00	-1	CT	Yes	Central Standard Time(Phenix Cityobserves Eastern time on ade factobasis)
3					Alaska1	09:00	-1	AKT	Yes	Most of state:UTC−09:00AKSTAlaska Standard Time
4					Alaska2	10:00	-1	HT		Aleutian Islands(west of 169°30' W):UTC−10:00HSTHawaii–Aleutian Standard Time
5					American Samoa	11:00	-1	ST	No	Samoa Standard Time
6					Arizona	07:00	-1	MT	Part	Mountain Standard Time. Except for theNavajo Nation, Arizona does not observe DST.
7					Arkansas	06:00	-1	CT	Yes	Central
8					California	08:00	-1	PT	Yes	Pacific Standard Time
9					Colorado	07:00	-1	MT	Yes	Mountain
10					Connecticut	05:00	-1	ET	Yes	Eastern Standard Time
11					Delaware	05:00	-1	ET	Yes	Eastern
12					District of Columbia	05:00	-1	ET	Yes	Eastern
13					Florida1	05:00	-1	ET	Yes	Big Bendand peninsula regions east of theApalachicola Riveralong with the portion ofGulf Countysouth of theIntracoastal Waterway:UTC−05:00ESTEastern Standard Time
14					Florida2	06:00	-1	CT	Yes	Florida PanhandleWest of the Apalachicola River except for the portion of Gulf County south of the Intracoastal Waterway:UTC−06:00CSTCentral Standard Time
15					Georgia	05:00	-1	ET	Yes	Eastern
16					Guam	10:00	1	ChT	No	Chamorro Standard Time
17					Hawaii	10:00	-1	HT	No	Hawaii–Aleutian
18					Idaho1	07:00	-1	MT	Yes	Most of state:UTC−07:00MSTMountain Standard Time (A legislative error in 1923 in theCalder Actseemingly put this area atUTC−06:00CST, and was corrected by Congress in 2007.[1])15 U.S.C.ch. 6, § 264. MST has been observed since 1923.
19					Idaho2	08:00	-1	PT	Yes	North of the Salmon River, that is between the Oregon state border and the Idaho County/Lemhi County border; west of the Idaho County/Lemhi County border, that is between the Salmon River and the Montana state border:UTC−08:00PSTPacific Standard Time
20					Illinois	06:00	-1	CT	Yes	Central
21					Indiana1	05:00	-1	ET	Yes	Most of state:UTC−05:00ESTEastern Standard Time
22					Indiana2	06:00	-1	CT	Yes	Northwest and southwest corners:UTC−06:00CSTCentral Standard Time (SeeTime in Indianafor more information)
23					Iowa	06:00	-1	CT	Yes	Central
24					Kansas1	06:00	-1	CT	Yes	Most of state:UTC−06:00CSTCentral Standard Time
25					Kansas2	07:00	-1	MT	Yes	Greeley,Hamilton,ShermanandWallacecounties:UTC−07:00MSTMountain Standard Time
26					Kentucky1	05:00	-1	ET	Yes	Eastern 60% of state:UTC−05:00ESTEastern Standard Time
27					Kentucky2	06:00	-1	CT		Western 40% of state:UTC−06:00CSTCentral Standard Time
28					Louisiana	06:00	-1	CT	Yes	Central
29					Maine	05:00	-1	ET	Yes	Eastern
30					Maryland	05:00	-1	ET	Yes	Eastern
31					Massachusetts	05:00	-1	ET	Yes	Eastern
32					Michigan1	05:00	-1	ET	Yes	Most of state:UTC−05:00ESTEastern Standard Time
33					Michigan2	06:00	-1	CT	Yes	Upper Peninsula counties borderingWisconsin:UTC−06:00CSTCentral Standard Time
34					Minnesota	06:00	-1	CT	Yes	Central
35					Mississippi	06:00	-1	CT	Yes	Central
36					Missouri	06:00	-1	CT	Yes	Central
37					Montana	07:00	-1	MT	Yes	Mountain
38					Nebraska1	06:00	-1	CT	Yes	Most of state:UTC−06:00CSTCentral Standard Time
39					Nebraska2	07:00	-1	MT	Yes	Nebraska Panhandle, counties withColoradoas a western boundary, and the westernSand Hills:UTC−07:00MSTMountain Standard Time
40					Nevada1	07:00	-1	MT	Yes	West Wendovercity limits:UTC−07:00MSTMountain Standard Time
41					Nevada2	08:00	-1	PT	Yes	Most of state:UTC−08:00PSTPacific Standard Time
42					New Hampshire	05:00	-1	ET	Yes	Eastern
43					New Jersey	05:00	-1	ET	Yes	Eastern
44					New Mexico	07:00	-1	MT	Yes	Mountain
45					New York	05:00	-1	ET	Yes	Eastern
46					North Carolina	05:00	-1	ET	Yes	Eastern
47					North Dakota1	06:00	-1	CT	Yes	Most of state:UTC−06:00CSTCentral Standard Time
48					North Dakota2	07:00	-1	MT	Yes	West of theMissouri River(exceptMortonandOlivercounties, and parts ofDunn,McKenzie, andSiouxcounties:UTC−07:00MSTMountain Standard Time
49					Northern MarianaIslands	10:00	1	ChT	No	Chamorro
50					Ohio	05:00	-1	ET	Yes	Eastern
51					Oklahoma	06:00	-1	CT	Yes	Central (Kentonobserves Mountain time on ade factobasis)
52					Oregon1	07:00	-1	MT	Yes	Northern 80% ofMalheur County:UTC−07:00MSTMountain Standard Time
53					Oregon2	08:00	-1	PT	Yes	Most of state:UTC−08:00PSTPacific Standard Time
54					Pennsylvania	05:00	-1	ET	Yes	Eastern
55					Puerto Rico	04:00	-1	AT	No	Atlantic Standard Time
56					Rhode Island	05:00	-1	ET	Yes	Eastern
57					South Carolina	05:00	-1	ET	Yes	Eastern
58					South Dakota1	06:00	-1	CT	Yes	Eastern half of state:UTC−06:00CSTCentral Standard Time
59					South Dakota2	07:00	-1	MT	Yes	Western half of state:UTC−07:00MSTMountain Standard Time
60					Tennessee1	05:00	-1	ET	Yes	East Tennessee, exceptBledsoe,Cumberland, andMarionCounties:UTC−05:00ESTEastern Standard Time
61					Tennessee2	06:00	-1	CT	Yes	Most of state:UTC−06:00CSTCentral Standard Time
62					Texas1	06:00	-1	CT	Yes	Most of state:UTC−06:00CSTCentral Standard Time
63					Texas2	07:00	-1	MT	Yes	El PasoandHudspethcounties:UTC−07:00MSTMountain Standard Time
64					U.S. Minor Outlying Islands1	0.5	1		No	Wake Island:UTC+12:00(Wake Island Time Zone)
65					U.S. Minor Outlying Islands2	0.5	-1		No	Baker IslandandHowland Island:UTC−12:00
66					U.S. Minor Outlying Islands3	11:00	-1	ST	No	Jarvis Island,Kingman Reef,Midway Atoll, andPalmyra Atoll:UTC−11:00(Samoa Time)
67					U.S. Minor Outlying Islands4	10:00	-1	HT	No	Johnston Atoll:UTC−10:00(Hawaii–Aleutian Time)
68					U.S. Minor Outlying Islands5	05:00	-1	ET	No
69					Utah	07:00	-1	MT	Yes	Mountain
70					Vermont	05:00	-1	ET	Yes	Eastern
71					Virgin Islands (U.S.)	04:00	-1	AT	No	Atlantic
72					Virginia	05:00	-1	ET	Yes	Eastern
73					Washington	08:00	-1	PT	Yes	Pacific
74					West Virginia	05:00	-1	ET	Yes	Eastern
75					Wisconsin	06:00	-1	CT	Yes	Central
76					Wyoming	07:00	-1	MT	Yes	Mountain
Sheet4

Cell Formulas
Range	Formula
C2	C2	=A2+XLOOKUP(B2,E2:E76,F2:F76)*XLOOKUP(B2,E2:E76,G2:G76)

 

[MARK858]

Or with a starting state

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Entry time	Entry State	UTC time	Destination State	Result		State / federal district or territory	Time Offsets	Plus or minus		DST	Details
2	09:00	Alaska1	18:00	Alabama	12:00		Alabama	06:00	-1	CT	Yes	Central Standard Time(Phenix Cityobserves Eastern time on ade factobasis)
3	10:00	Alaska2	20:00	Alabama	14:00		Alaska1	09:00	-1	AKT	Yes	Most of state:UTC−09:00AKSTAlaska Standard Time
4	11:00	Florida1	16:00	Alabama	10:00		Alaska2	10:00	-1	HT		Aleutian Islands(west of 169°30' W):UTC−10:00HSTHawaii–Aleutian Standard Time
5	12:00	Alabama	18:00	Alabama	12:00		American Samoa	11:00	-1	ST	No	Samoa Standard Time
6							Arizona	07:00	-1	MT	Part	Mountain Standard Time. Except for theNavajo Nation, Arizona does not observe DST.
7							Arkansas	06:00	-1	CT	Yes	Central
8							California	08:00	-1	PT	Yes	Pacific Standard Time
9							Colorado	07:00	-1	MT	Yes	Mountain
10							Connecticut	05:00	-1	ET	Yes	Eastern Standard Time
11							Delaware	05:00	-1	ET	Yes	Eastern
12							District of Columbia	05:00	-1	ET	Yes	Eastern
13							Florida1	05:00	-1	ET	Yes	Big Bendand peninsula regions east of theApalachicola Riveralong with the portion ofGulf Countysouth of theIntracoastal Waterway:UTC−05:00ESTEastern Standard Time
14							Florida2	06:00	-1	CT	Yes	Florida PanhandleWest of the Apalachicola River except for the portion of Gulf County south of the Intracoastal Waterway:UTC−06:00CSTCentral Standard Time
15							Georgia	05:00	-1	ET	Yes	Eastern
16							Guam	10:00	1	ChT	No	Chamorro Standard Time
17							Hawaii	10:00	-1	HT	No	Hawaii–Aleutian
18							Idaho1	07:00	-1	MT	Yes	Most of state:UTC−07:00MSTMountain Standard Time (A legislative error in 1923 in theCalder Actseemingly put this area atUTC−06:00CST, and was corrected by Congress in 2007.[1])15 U.S.C.ch. 6, § 264. MST has been observed since 1923.
19							Idaho2	08:00	-1	PT	Yes	North of the Salmon River, that is between the Oregon state border and the Idaho County/Lemhi County border; west of the Idaho County/Lemhi County border, that is between the Salmon River and the Montana state border:UTC−08:00PSTPacific Standard Time
20							Illinois	06:00	-1	CT	Yes	Central
21							Indiana1	05:00	-1	ET	Yes	Most of state:UTC−05:00ESTEastern Standard Time
22							Indiana2	06:00	-1	CT	Yes	Northwest and southwest corners:UTC−06:00CSTCentral Standard Time (SeeTime in Indianafor more information)
23							Iowa	06:00	-1	CT	Yes	Central
24							Kansas1	06:00	-1	CT	Yes	Most of state:UTC−06:00CSTCentral Standard Time
25							Kansas2	07:00	-1	MT	Yes	Greeley,Hamilton,ShermanandWallacecounties:UTC−07:00MSTMountain Standard Time
26							Kentucky1	05:00	-1	ET	Yes	Eastern 60% of state:UTC−05:00ESTEastern Standard Time
27							Kentucky2	06:00	-1	CT		Western 40% of state:UTC−06:00CSTCentral Standard Time
28							Louisiana	06:00	-1	CT	Yes	Central
29							Maine	05:00	-1	ET	Yes	Eastern
30							Maryland	05:00	-1	ET	Yes	Eastern
31							Massachusetts	05:00	-1	ET	Yes	Eastern
32							Michigan1	05:00	-1	ET	Yes	Most of state:UTC−05:00ESTEastern Standard Time
33							Michigan2	06:00	-1	CT	Yes	Upper Peninsula counties borderingWisconsin:UTC−06:00CSTCentral Standard Time
34							Minnesota	06:00	-1	CT	Yes	Central
35							Mississippi	06:00	-1	CT	Yes	Central
36							Missouri	06:00	-1	CT	Yes	Central
37							Montana	07:00	-1	MT	Yes	Mountain
38							Nebraska1	06:00	-1	CT	Yes	Most of state:UTC−06:00CSTCentral Standard Time
39							Nebraska2	07:00	-1	MT	Yes	Nebraska Panhandle, counties withColoradoas a western boundary, and the westernSand Hills:UTC−07:00MSTMountain Standard Time
40							Nevada1	07:00	-1	MT	Yes	West Wendovercity limits:UTC−07:00MSTMountain Standard Time
41							Nevada2	08:00	-1	PT	Yes	Most of state:UTC−08:00PSTPacific Standard Time
42							New Hampshire	05:00	-1	ET	Yes	Eastern
43							New Jersey	05:00	-1	ET	Yes	Eastern
44							New Mexico	07:00	-1	MT	Yes	Mountain
45							New York	05:00	-1	ET	Yes	Eastern
46							North Carolina	05:00	-1	ET	Yes	Eastern
47							North Dakota1	06:00	-1	CT	Yes	Most of state:UTC−06:00CSTCentral Standard Time
48							North Dakota2	07:00	-1	MT	Yes	West of theMissouri River(exceptMortonandOlivercounties, and parts ofDunn,McKenzie, andSiouxcounties:UTC−07:00MSTMountain Standard Time
49							Northern MarianaIslands	10:00	1	ChT	No	Chamorro
50							Ohio	05:00	-1	ET	Yes	Eastern
51							Oklahoma	06:00	-1	CT	Yes	Central (Kentonobserves Mountain time on ade factobasis)
52							Oregon1	07:00	-1	MT	Yes	Northern 80% ofMalheur County:UTC−07:00MSTMountain Standard Time
53							Oregon2	08:00	-1	PT	Yes	Most of state:UTC−08:00PSTPacific Standard Time
54							Pennsylvania	05:00	-1	ET	Yes	Eastern
55							Puerto Rico	04:00	-1	AT	No	Atlantic Standard Time
56							Rhode Island	05:00	-1	ET	Yes	Eastern
57							South Carolina	05:00	-1	ET	Yes	Eastern
58							South Dakota1	06:00	-1	CT	Yes	Eastern half of state:UTC−06:00CSTCentral Standard Time
59							South Dakota2	07:00	-1	MT	Yes	Western half of state:UTC−07:00MSTMountain Standard Time
60							Tennessee1	05:00	-1	ET	Yes	East Tennessee, exceptBledsoe,Cumberland, andMarionCounties:UTC−05:00ESTEastern Standard Time
61							Tennessee2	06:00	-1	CT	Yes	Most of state:UTC−06:00CSTCentral Standard Time
62							Texas1	06:00	-1	CT	Yes	Most of state:UTC−06:00CSTCentral Standard Time
63							Texas2	07:00	-1	MT	Yes	El PasoandHudspethcounties:UTC−07:00MSTMountain Standard Time
64							U.S. Minor Outlying Islands1	0.5	1		No	Wake Island:UTC+12:00(Wake Island Time Zone)
65							U.S. Minor Outlying Islands2	0.5	-1		No	Baker IslandandHowland Island:UTC−12:00
66							U.S. Minor Outlying Islands3	11:00	-1	ST	No	Jarvis Island,Kingman Reef,Midway Atoll, andPalmyra Atoll:UTC−11:00(Samoa Time)
67							U.S. Minor Outlying Islands4	10:00	-1	HT	No	Johnston Atoll:UTC−10:00(Hawaii–Aleutian Time)
68							U.S. Minor Outlying Islands5	05:00	-1	ET	No
69							Utah	07:00	-1	MT	Yes	Mountain
70							Vermont	05:00	-1	ET	Yes	Eastern
71							Virgin Islands (U.S.)	04:00	-1	AT	No	Atlantic
72							Virginia	05:00	-1	ET	Yes	Eastern
73							Washington	08:00	-1	PT	Yes	Pacific
74							West Virginia	05:00	-1	ET	Yes	Eastern
75							Wisconsin	06:00	-1	CT	Yes	Central
76							Wyoming	07:00	-1	MT	Yes	Mountain
Sheet4

Cell Formulas
Range	Formula
C2:C5	C2	=$A2-XLOOKUP($B2,$G$2:$G$76,$H$2:$H$76)*XLOOKUP($D2,$G$2:$G$76,$I$2:$I$76)
E2:E5	E2	=$C2+XLOOKUP($D2,$G$2:$G$76,$H$2:$H$76)*XLOOKUP($D2,$G$2:$G$76,$I$2:$I$76)

 

[Logit]

The downloadable file contains data comprised of ZIP, STATE, CITY, LAT, LONG, TimeZone, UTC Time in H1 and the local time for each zip code in Column G.
There are two buttons ... one that starts the macro and one that stops the macro.

From the HOME tab you can select "FIND & SELECT" and use the search function to locate the zip code of interest. The cursor will jump to and highlight
the zip code searched for allowing you to view the local time in that area.

You can position the search form to the right of all the data and leave it displayed allowing quick access to the search feature.

The macro is set to auto update every 30 seconds. This will give you sufficient time to search for a zip code without being interrupted by the update action. Depending on when you click
START will determine how close to the beginning of an actual minute the time display will be. I trust your 'time requirement' does not require an exact time ?

When closing the workbook you may be presented with a notice regarding ActiveX components, Objects, etc, etc. Just click OK and ignore the message.

Option Explicit

Dim KeepUpdating As Boolean

Sub StartUpdatingTime()
    KeepUpdating = True
    Call UpdateTime
End Sub

Sub StopUpdatingTime()
    KeepUpdating = False
End Sub

Sub UpdateTime()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim utcTime As Date
    Dim timeZone As String

    Set ws = Worksheets("Sheet1")
    utcTime = Now - TimeZoneOffset()
    ws.Range("H1").Value = utcTime

    ' Determine the last row in column F with data
    lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row

    ' Loop through each time zone in column F and calculate local time in column G
    For i = 1 To lastRow
        timeZone = ws.Cells(i, "F").Value
        Select Case LCase(timeZone)
            Case "eastern": ws.Cells(i, "G").Value = utcTime - TimeSerial(5, 0, 0)
            Case "central": ws.Cells(i, "G").Value = utcTime - TimeSerial(6, 0, 0)
            Case "mountain": ws.Cells(i, "G").Value = utcTime - TimeSerial(7, 0, 0)
            Case "pacific": ws.Cells(i, "G").Value = utcTime - TimeSerial(8, 0, 0)
            Case "alaska": ws.Cells(i, "G").Value = utcTime - TimeSerial(9, 0, 0)
            Case "hawaii": ws.Cells(i, "G").Value = utcTime - TimeSerial(10, 0, 0)
            Case "atlantic": ws.Cells(i, "G").Value = utcTime - TimeSerial(4, 0, 0)
            Case Else:
        End Select
    Next i

    If KeepUpdating Then
        Application.OnTime Now + TimeValue("00:01:00"), "UpdateTime"
    End If
End Sub

Function TimeZoneOffset() As Double
    TimeZoneOffset = TimeSerial(-5, 0, 0) ' Adjusting by +5 hours
End Function

Download file : Internxt Drive – Private & Secure Cloud Storage

 

[Logit]

I'm doing some updating to the workbook. I'll post a new download link soon.

 

[Logit]

The new download link to updated workbook : Internxt Drive – Private & Secure Cloud Storage

This version does not display the information message when closing. Also provided two new columns: Standard Time and Daylight Savings Time.

 

[Logit]

I believe I have finally worked out all the small bugs in the project. Here is the final version of the workbook :

Internxt Drive – Private & Secure Cloud Storage

 

[Logit]

Alright ... here is the absolute final version. Unless you require some changes, I am done now.

Internxt Drive – Private & Secure Cloud Storage

 

[Logit]

I just realized the last copy of the workbook posted is incorrect. Not certain how or where I selected the wrong file. This new link is absolutely the correct file.
Whew .....

Internxt Drive – Private & Secure Cloud Storage

 

[Logit]

Dear DPChristman and Excel Forum :

I offer my most sincere apologies to all for my sloppy attempts of providing an answer to DPChristman's post.

Generally I am able to create a solution to posts in short order. This time that was not the case. Each time
I posted an answer it was honestly felt the answer was complete. They weren't as is obvious from following
this thread.

Again I apologize. There is a health issue that could be offered as a reason for my shortcomings but doing so
wouldn't change anything ... the errors are still there.

Here I have (as the old saying goes - "If you don't succeed the first time, try ... try ... again.") made numerous
changes to the coding and the workbook since my last posting. I checked ... double checked ... and triple checked
everything. Then I checked it again - not willing to accept anything prematurely.

I am hesitant to post the updated/edited version of the workbook again for obvious reasons. BUT ... I owe
DPChristman a working answer to his request. And I owe the same to the 'Mr Excel Forum'.

So ... here goes. May the Excel gods be with me ...

(If it is possible for a Moderator to delete my previous posts ... that would be a 'god send'. I would be forever
grateful.)

A portion of the code :

Sub Update_Time()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim utcTime As Date
    Dim timeZone As String
    Dim sn
    
    Set ws = Worksheets("Sheet1")
    utcTime = Now - TimeZoneOffset()
    
ws.Range("K1").Value = ""
ws.Range("K2").Value = ""
    
' Determine array size from column F data
    sn = ws.Range("F2", ws.Range("F" & ws.Rows.Count).End(xlUp)).Resize(, 2)

' Loop through each time zone in column F and calculate local time in column G
    For i = 1 To UBound(sn)
        'timeZone = ws.Cells(i, "F").Value
        Select Case LCase(sn(i, 1))
            Case "eastern": sn(i, 2) = utcTime - TimeSerial(5, 0, 0)
            Case "central": sn(i, 2) = utcTime - TimeSerial(6, 0, 0)
            Case "mountain": sn(i, 2) = utcTime - TimeSerial(7, 0, 0)
            Case "pacific": sn(i, 2) = utcTime - TimeSerial(8, 0, 0)
            Case "alaska": sn(i, 2) = utcTime - TimeSerial(9, 0, 0)
            Case "hawaii": sn(i, 2) = utcTime - TimeSerial(10, 0, 0)
            Case "atlantic": sn(i, 2) = utcTime - TimeSerial(4, 0, 0)
            Case Else:
        End Select
    Next i
    ws.Range("F2").Resize(UBound(sn), 2) = sn
    
    'ws.Range("K2").Value = utcTime
  
    Clock

End Sub

Internxt Drive – Private & Secure Cloud Storage