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"))))))
Posted by at

55 comments:

  1. AnonymousSeptember 24, 2014 at 3:21 PM

    Thank you so much, that is incredibly helpful!

    ReplyDelete
  2. UnknownMay 15, 2015 at 1:27 PM

    Nice. Worked like a charm.

    ReplyDelete
  3. RussyMay 20, 2015 at 5:19 PM

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

    ReplyDelete
  4. AnonymousJune 8, 2015 at 3:01 PM

    THANKS IT WORKS

    ReplyDelete
  5. jmJune 16, 2015 at 5:44 AM

    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. Alexander TaylorJune 22, 2015 at 10:04 AM

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

      Delete
  6. UnknownJuly 16, 2015 at 1:06 PM

    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. Alexander TaylorJuly 16, 2015 at 2:47 PM

      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. AnonymousMarch 28, 2016 at 7:11 AM

      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
  7. UnknownJuly 16, 2015 at 2:55 PM

    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. UnknownJuly 16, 2015 at 2:56 PM

      B2, C2, D2 are the corresponding columns sorry.

      Delete
    2. AnonymousOctober 2, 2015 at 7:21 AM

      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. Alexander TaylorOctober 3, 2015 at 5:59 PM

      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. KblogOctober 15, 2015 at 6:23 AM

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

      Delete
  8. UnknownNovember 3, 2015 at 10:50 AM

    Thank you. This is exactly what I needed.

    ReplyDelete
  9. UnknownNovember 16, 2015 at 2:23 PM

    Slick, Quick and Helpful

    Thanks!!

    ReplyDelete
  10. JDJanuary 5, 2016 at 2:54 PM

    fantabulous work!!! it worked

    ReplyDelete
  11. Technology At It's BestFebruary 5, 2016 at 6:49 AM

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

    ReplyDelete
  12. Alexander TaylorFebruary 5, 2016 at 10:05 AM

    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
  13. Technology At It's BestFebruary 5, 2016 at 11:43 AM

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

    ReplyDelete
  14. TheDude5000February 19, 2016 at 11:40 AM

    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
  15. Marianne van HasseltFebruary 25, 2016 at 1:38 PM

    This comment has been removed by the author.

    ReplyDelete
  16. AnonymousFebruary 25, 2016 at 1:39 PM

    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. Alexander TaylorMarch 6, 2016 at 4:11 PM

      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
  17. BrendaPalmeriMay 26, 2016 at 5:55 AM

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

    ReplyDelete
  18. AnonymousJune 8, 2016 at 3:27 PM

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

    ReplyDelete
    Replies
    1. Alexander TaylorJune 8, 2016 at 4:24 PM

      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
  19. AnonymousJune 9, 2016 at 5:34 AM

    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. Alexander TaylorAugust 11, 2016 at 7:59 PM

      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
  20. UnknownAugust 10, 2016 at 12:59 PM

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

    ReplyDelete
    Replies
    1. Alexander TaylorAugust 11, 2016 at 7:55 PM

      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
    2. AnonymousSeptember 24, 2018 at 4:30 PM

      Arizona is actually not MST either. Similar to Hawaii, it should have its own time zone, as it is one of the only states that does not observe daylight savings!

      Delete
  21. AnonymousSeptember 21, 2016 at 7:17 AM

    Very useful, thank you!

    ReplyDelete
  22. AnonymousDecember 28, 2016 at 7:06 PM

    =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
  23. UnknownMarch 24, 2017 at 3:04 PM

    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. Alexander TaylorMarch 24, 2017 at 8:46 PM

      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
  24. AnonymousApril 10, 2018 at 10:13 AM

    Big time saver, thank you!

    ReplyDelete
  25. UnknownAugust 7, 2018 at 4:01 PM

    What if the starting cell would be on H?

    ReplyDelete
  26. UnknownDecember 6, 2018 at 5:42 AM

    In case the states are spelled out in full name format here you go. I even threw in a few Canadian locations as well. Just make sure to change the cell number!!! Thanks for this formula, saved me loads of time.

    =if(ISNUMBER(SEARCH(AL83,"Washington,Oregon,California,Nevada")),"PDT",if(ISNUMBER(SEARCH(AL83,"Montana,Idaho,Wyoming,Utah,Colorado,Arizona,New Mexico")),"MDT",if(ISNUMBER(SEARCH(AL83,"North Dakota,South Dakota,Nebraska,Kansas,Oklahoma,Texas,Minnesota,Iowa,Missouri,Arkansas,Louisiana,Wisconsin,Illinois,Tennessee,Mississippi,Alabama")),"CDT",if(ISNUMBER(SEARCH(AL83,"Michigan,Indiana,Ohio,Pennsylvania,New York,Vermont,Maine,New Hampshire,Massachusetts,Rhode Island,Connecticut,Kentucky,New Jersey,Delaware,Maryland,West Virginia,Virginia,North Carolina,South Carolina,Georgia,Florida,District of Columbia")),"EDT",if(ISNUMBER(SEARCH(AL83,"Ontario,British Columbia,Quebec")),"CANADA",if(ISNUMBER(SEARCH(AL83,"Alaska")),"AKDT",if(ISNUMBER(SEARCH(AL83,"Hawaii")),"HST","")))))))

    ReplyDelete
  27. TahaMarch 20, 2019 at 4:30 PM

    If like me you have an entire address on the cell, use RIGHT to extract the last 2 characters of the string.

    Should look like this:

    =if(ISNUMBER(SEARCH(RIGHT(F2,2),"WA,OR,CA,NV")),"PDT",if(ISNUMBER(SEARCH(RIGHT(F2,2),"MT,ID,WY,UT,CO,AZ,NM")),"MDT",if(ISNUMBER(SEARCH(RIGHT(F2,2),"ND,SD,NE,KS,OK,TX,MN,IA,MO,AR,LA,WI,IL,TN,MS,AL")),"CDT",if(ISNUMBER(SEARCH(RIGHT(F2,2),"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(RIGHT(F2,2),"AK")),"AKDT",if(ISNUMBER(SEARCH(RIGHT(F2,2),"HI")),"HST",""))))))

    Tested on Google Sheets.

    ReplyDelete
  28. ilucas10ffApril 25, 2019 at 7:13 AM

    AMAZING, thank you so much this was extremely helpful.

    ReplyDelete
  29. AnonymousNovember 15, 2019 at 9:25 AM

    This is awesome!! thank yo so much!

    ReplyDelete
  30. jjortiz422February 6, 2020 at 10:55 AM

    Thank you for work. Worked in about a min.

    ReplyDelete
  31. jjortiz422February 6, 2020 at 10:55 AM

    Thank you for work. Worked in about a min.

    ReplyDelete
  32. lilarsonOctober 12, 2020 at 8:19 PM

    Perfecto! Thanks everyone!

    ReplyDelete
  33. UnknownMarch 10, 2021 at 8:52 AM

    This is great thanks!

    ReplyDelete
  34. AnonymousMarch 11, 2021 at 8:55 PM

    Really appreciate for sharing the formula.

    ReplyDelete
  35. Doug HenningsenJune 18, 2021 at 4:46 PM

    Huge thanks...worked like a charm in Google Sheets.

    ReplyDelete
  36. UnknownJuly 5, 2021 at 8:12 AM

    I have timezone but I need City or state and I have timezone data of entire planet

    ReplyDelete
  37. UnknownNovember 18, 2021 at 10:52 AM

    Thanks man, this saved me a lot of time!

    ReplyDelete
  38. Cisco StudentJanuary 24, 2022 at 4:21 PM

    This is awesome! You deserve a fine cigar and a beer (of course, much more than this but still, just saying!). Thanks!

    ReplyDelete
  39. NJarrotOctober 9, 2023 at 9:27 AM

    Thank you for posting this! Worked perfectly and saved me so much time. 🙌

    ReplyDelete
  40. AnonymousJanuary 2, 2024 at 8:26 PM

    this will not work on my computer please tell me why
    blanks come up as pacific
    anything with text comes up as false
    please help

    ReplyDelete

Subscribe to: Post Comments (Atom)