2014-03-25

how to convert state to timezone using excel formula

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"))))))

38 comments:

  1. Thank you so much, that is incredibly helpful!

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

    ReplyDelete
  3. Alex, 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

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

      Delete
  4. This 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

    ReplyDelete
    Replies
    1. Not sure I understand the second part of your question, but try this:
      =if(isblank(a1),"",FORMULA_HERE)
      where FORMULA_HERE is a formula from above, without the leading "=" sign.

      Delete
    2. 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"

      =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","")))))))

      Delete
  5. Works perfect thank you.

    That 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.

    ReplyDelete
    Replies
    1. B2, C2, D2 are the corresponding columns sorry.

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

      Delete
    3. Here you go: http://youtu.be/O4-nlXaqEpE
      Never really done this before so it's not the best video, but hope it helps.

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

      Delete
  6. Thank you. This is exactly what I needed.

    ReplyDelete
  7. Slick, Quick and Helpful

    Thanks!!

    ReplyDelete
  8. fantabulous work!!! it worked

    ReplyDelete
  9. Is there a way to get current localtime of a timezone?

    ReplyDelete
  10. i'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.

    ReplyDelete
  11. to open with the actual current time whenever the spreadsheet updated would be perfect!!!

    ReplyDelete
  12. for tsql fans, I converted this to sql server case function:

    DECLARE @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

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. how 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

    ReplyDelete
    Replies
    1. after 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.
      video: http://youtu.be/O4-nlXaqEpE

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

    ReplyDelete
  16. Very entertaining information. found a lot of things for yourself useful. excel classes

    ReplyDelete
  17. Thanks for your posting. What is the purpose of having the ISNUMBER function within the formula?

    ReplyDelete
    Replies
    1. you'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.

      Delete
  18. Appreciate 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...

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

    ReplyDelete
    Replies
    1. 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)

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

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

    ReplyDelete
    Replies
    1. oops. actually i suppose it should probably say MST for the other states as well if the excel formula is evaluated in winter time?
      thanks!
      if anyone sends me a corrected formula i'll put it up.

      Delete
  20. Very useful, thank you!

    ReplyDelete
  21. =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"))))))

    ReplyDelete
    Replies
    1. thanks. added this as alternative in the post.

      Delete
  22. Please 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!

    ReplyDelete
    Replies
    1. click 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.
      If I remember correctly it's shown here: http://youtu.be/O4-nlXaqEpE

      Delete