Here is an Excel function to get a timezone based on the state. Some states have multiple timezones, so I just chose the timezone that looked like it took up most of the state on the timezone map.

This takes a two-letter state and converts it to "Pacific", "Mountain", "Central", "Alaska" or "Hawaii".

=if(ISNUMBER(SEARCH(A1,"WA,OR,CA,NV")),"Pacific",if(ISNUMBER(SEARCH(A1,"MT,ID,WY,UT,CO,AZ,NM")),"Mountain",if(ISNUMBER(SEARCH(A1,"ND,SD,NE,KS,OK,TX,MN,IA,MO,AR,LA,WI,IL,TN,MS,AL")),"Central",if(ISNUMBER(SEARCH(A1,"MI,IN,OH,PA,NY,VT,ME,NH,MA,RI,CT,KY,NJ,DE,MD,WV,VA,NC,SC,GA,FL,DC")),"Eastern",if(ISNUMBER(SEARCH(A1,"AK")),"Alaska",if(ISNUMBER(SEARCH(A1,"HI")),"Hawaii",""))))))

This takes a two-letter state and converts it to "PDT", "MDT", "CDT", "AKDT" or "HST".

=if(ISNUMBER(SEARCH(A1,"WA,OR,CA,NV")),"PDT",if(ISNUMBER(SEARCH(A1,"MT,ID,WY,UT,CO,AZ,NM")),"MDT",if(ISNUMBER(SEARCH(A1,"ND,SD,NE,KS,OK,TX,MN,IA,MO,AR,LA,WI,IL,TN,MS,AL")),"CDT",if(ISNUMBER(SEARCH(A1,"MI,IN,OH,PA,NY,VT,ME,NH,MA,RI,CT,KY,NJ,DE,MD,WV,VA,NC,SC,GA,FL,DC")),"EDT",if(ISNUMBER(SEARCH(A1,"AK")),"AKDT",if(ISNUMBER(SEARCH(A1,"HI")),"HST",""))))))

Copy and paste the formula into a text editor such as Microsoft Word, and then do find-replace to change A1 to whichever cell has the state you want to replace, such as D2. Then plop the formula into excel.

Currently, the formula gives a blank value if the state isn't the 2-letter abbreviation of any of the 50 states in the U.S. To make it say something, like "ERROR", add some text in between the last set of quotes in the formula, after "Hawaii". E.g. ... "HI")),"Hawaii","ERROR!")))))...

If those formulas don't work, you can try something like this thanks to the person in the comments who suggested it. This seems to work on Google Sheets and Excel Online.

=if(OR(A1="CT", A1="DC", A1="DE", A1="FL", A1="GA", A1="IN", A1="KY", A1="MA", A1="MD", A1="ME", A1="MI", A1="NC", A1="NH", A1="NJ", A1="NY", A1="OH", A1="PA", A1="RI", A1="SC", A1="VA", A1="VT", A1="WV"),"Eastern",

if(OR(A1="AL", A1="AR", A1="IA", A1="IL", A1="KS", A1="LA", A1="MN", A1="MO", A1="MS", A1="ND", A1="NE", A1="OK", A1="SD", A1="TN", A1="TX", A1="WI"),"Central",

if(OR(A1="AZ", A1="CO", A1="ID", A1="MT", A1="NM", A1="UT", A1="WY"),"Mountain",

if(OR(A1="CA", A1="NV", A1="OR", A1="WA"),"Pacific",

if(OR(A1="AK"),"Alaska",

if(OR(A1="HI"),"Hawaii","Unknown"))))))

Thank you so much, that is incredibly helpful!

ReplyDeleteNice. Worked like a charm.

ReplyDeleteThanks for this! Just a heads-up that you forgot "DC" for the Eastern time zone.

ReplyDeletefixed, thanks!

DeleteTHANKS IT WORKS

ReplyDeleteAlex, your formula is a great resource. I must differ on one of your state assignments. Most of Kentucky lies in the Eastern time zone as does its major cities. Analysis on the following site shows most of its population is in the Eastern time zone: http://answers.google.com/answers/threadview?id=714986

ReplyDeletethank you john! fixed the formulas. suggestions from others are welcome as well--i just eyeballed a map for this.

DeleteThis is extremely helpful. Is there a way to leave blank for cells that contain no data? Currently it defaults all of the blanks under the state column in my table to pacific. This also changes the "current time" column data for each entry

ReplyDeleteNot sure I understand the second part of your question, but try this:

Delete=if(isblank(a1),"",FORMULA_HERE)

where FORMULA_HERE is a formula from above, without the leading "=" sign.

Change all 'D2' references to the cell where your two-digit state value is stored. This version of the function will ***leave timezone field blank if there is no state value***. I believe original function was defaulting to "Pacific"

Delete=IF(ISBLANK(D2),"",IF(ISNUMBER(SEARCH(D2,"WA,OR,CA,NV")),"Pacific",IF(ISNUMBER(SEARCH(D2,"MT,ID,WY,UT,CO,AZ,NM")),"Mountain",IF(ISNUMBER(SEARCH(D2,"ND,SD,NE,KS,OK,TX,MN,IA,MO,AR,LA,WI,IL,TN,MS,AL")),"Central",IF(ISNUMBER(SEARCH(D2,"MI,IN,OH,PA,NY,VT,ME,NH,MA,RI,CT,KY,NJ,DE,MD,WV,VA,NC,SC,GA,FL,DC")),"Eastern",IF(ISNUMBER(SEARCH(D2,"AK")),"Alaska",IF(ISNUMBER(SEARCH(D2,"HI")),"Hawaii","")))))))

Works perfect thank you.

ReplyDeleteThat was the only questions, I was stating that I used your formula to create a separate column that auto inputs that current time in the state based on the time zone. So when user selects let's say WA in A2, A3 shows Pacific and A4 shows current time in Pacific.

B2, C2, D2 are the corresponding columns sorry.

DeleteI would like to try this but am limited in my Excel knowledge. Could you provide a step by step? Would be greatly appreciated

DeleteHere you go: http://youtu.be/O4-nlXaqEpE

DeleteNever really done this before so it's not the best video, but hope it helps.

Much appreciated. I had a 200 line spreadsheet that needed to be sorted instead of me doing it manually. Thanks again Alexander!

DeleteThank you. This is exactly what I needed.

ReplyDeleteSlick, Quick and Helpful

ReplyDeleteThanks!!

fantabulous work!!! it worked

ReplyDeleteIs there a way to get current localtime of a timezone?

ReplyDeletei'm sure it's possible but all i see online is a lot of hacks involving some math. also it would only update to the actual current time whenever the spreadsheet updated. if that's not a problem for you, and if it has to be in excel/sheets because opening the world clock in a new tab isn't sufficient ( http://www.timeanddate.com/worldclock/ ) then might be time to study up on excel/sheets functions and mechanics of how dates and times are handled.

ReplyDeleteto open with the actual current time whenever the spreadsheet updated would be perfect!!!

ReplyDeletefor tsql fans, I converted this to sql server case function:

ReplyDeleteDECLARE @st varchar(2) = 'NC'

SELECT

CASE

WHEN @st IN ('WA','OR','CA','NV') THEN 'PST'

WHEN @st IN ('MT','ID','WY','UT','CO','AZ','NM') THEN 'MST'

WHEN @st IN ('ND','SD','NE','KS','OK','TX','MN','IA','MO','AR','LA','WI','IL','TN','MS','AL') THEN 'CST'

WHEN @st IN ('MI','IN','OH','PA','NY','VT','ME','NH','MA','RI','CT','KY','NJ','DE','MD','WV','VA','NC','SC','GA','FL','DC') THEN 'EST'

WHEN @st IN ('AK') THEN 'AKST'

WHEN @st IN ('HI') THEN 'HST'

ELSE '?'

END

This comment has been removed by the author.

ReplyDeletehow do you make the cell referential(?) so that you can copy paste it down a column and it refers to another cell in the same row without having to adjust each formula

ReplyDeleteafter you put down the formula for the first column, click and hold the lower right of the cell (your mouse pointer will change icons to a + sign) and drag down, and it will change the formula automatically.

Deletevideo: http://youtu.be/O4-nlXaqEpE

works perfectly! now I can see how to create a formula for similar functions needed

ReplyDeleteVery entertaining information. found a lot of things for yourself useful. excel classes

ReplyDeleteThanks for your posting. What is the purpose of having the ISNUMBER function within the formula?

ReplyDeleteyou're welcome :) if the SEARCH function fails to find the searched-for text, then it returns "#VALUE!" which is not a number, otherwise it returns the number position where the searched-for text was found. ISNUMBER(SEARCH(...)) converts it to true or false instead of some number or #VALUE!. then that it's true or false, it can be passed to the "IF" function.

DeleteAppreciate the feedback. In your formula are you assuming a numerical value could be within the referenced cell? If the cells just text, couldn't the formula exclude the ISNUMBER function and be simpler like...

ReplyDelete=IF(OR(AA2="WA", AA2="CA"), "Pacific", IF(OR(AA2="NY", AA2="DC"), "EST", "Neither"))

that looks more robust to me! it would be more lengthy, which is bad for blog posts but good for code (and therefore also good for blog posts :p)

Deleteif you want to send me the updated formula i could post both up.

This is very helpful, one correction however, Arizona is not MDT, it is MST. It does not observe Daylight Saving time.

ReplyDeleteoops. actually i suppose it should probably say MST for the other states as well if the excel formula is evaluated in winter time?

Deletethanks!

if anyone sends me a corrected formula i'll put it up.

Very useful, thank you!

ReplyDelete=if(OR(A1="CT", A1="DC", A1="DE", A1="FL", A1="GA", A1="IN", A1="KY", A1="MA", A1="MD", A1="ME", A1="MI", A1="NC", A1="NH", A1="NJ", A1="NY", A1="OH", A1="PA", A1="RI", A1="SC", A1="VA", A1="VT", A1="WV"),"Eastern",

ReplyDeleteif(OR(A1="AL", A1="AR", A1="IA", A1="IL", A1="KS", A1="LA", A1="MN", A1="MO", A1="MS", A1="ND", A1="NE", A1="OK", A1="SD", A1="TN", A1="TX", A1="WI"),"Central",

if(OR(A1="AZ", A1="CO", A1="ID", A1="MT", A1="NM", A1="UT", A1="WY"),"Mountain",

if(OR(A1="CA", A1="NV", A1="OR", A1="WA"),"Pacific",

if(OR(A1="AK"),"Alaska",

if(OR(A1="HI"),"Hawaii","Unknown"))))))

thanks. added this as alternative in the post.

DeletePlease help if you can. How would I apply this formula to an entire column? In this example I am trying to apply it to every cell in column K. Thank you for any help you can provide!

ReplyDeleteclick the lower right corner of cell K1 (or wherever you pasted the formula) and drag down. it will copy the formula to those cells too and adjust the numbers.

DeleteIf I remember correctly it's shown here: http://youtu.be/O4-nlXaqEpE