Formula for state abbreviation and zip codes into time zone

I've been trying to create a column formula for two scenerios. One to turn state abbreviations into time zones in a seperate column. Then turn zip codes into time zones in a seperate column. I've tried using this formula for turning states into time zones without any luck. Can you advise?

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

I will then need to create a formula for ((Applicant Zip Code)) based on the numeric zip code spans. Thank you so much!

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    Here is a version of the formula you listed modified for Smartsheet.

    =IF(OR([Applicant State/Province]@row = "WA", [Applicant State/Province]@row = "OR", [Applicant State/Province]@row = "CA", [Applicant State/Province]@row = "NV"), "PST", IF(OR([Applicant State/Province]@row = "MT", [Applicant State/Province]@row = "ID", [Applicant State/Province]@row = "WY", [Applicant State/Province]@row = "UT", [Applicant State/Province]@row = "CO", [Applicant State/Province]@row = "AZ", [Applicant State/Province]@row = "NM"), "MST", IF(OR([Applicant State/Province]@row = "ND", [Applicant State/Province]@row = "SD", [Applicant State/Province]@row = "NE", [Applicant State/Province]@row = "KS", [Applicant State/Province]@row = "OK", [Applicant State/Province]@row = "TX", [Applicant State/Province]@row = "MN", [Applicant State/Province]@row = "IA", [Applicant State/Province]@row = "MO", [Applicant State/Province]@row = "AR", [Applicant State/Province]@row = "LA", [Applicant State/Province]@row = "WI", [Applicant State/Province]@row = "IL", [Applicant State/Province]@row = "TN", [Applicant State/Province]@row = "MS", [Applicant State/Province]@row = "AL"), "CST", IF(OR([Applicant State/Province]@row = "MI", [Applicant State/Province]@row = "IN", [Applicant State/Province]@row = "OH", [Applicant State/Province]@row = "PA", [Applicant State/Province]@row = "NY", [Applicant State/Province]@row = "VT", [Applicant State/Province]@row = "ME", [Applicant State/Province]@row = "NH", [Applicant State/Province]@row = "MA", [Applicant State/Province]@row = "RI", [Applicant State/Province]@row = "CT", [Applicant State/Province]@row = "KY", [Applicant State/Province]@row = "NJ", [Applicant State/Province]@row = "DE", [Applicant State/Province]@row = "MD", [Applicant State/Province]@row = "WV", [Applicant State/Province]@row = "VA", [Applicant State/Province]@row = "NC", [Applicant State/Province]@row = "SC", [Applicant State/Province]@row = "GA", [Applicant State/Province]@row = "FL", [Applicant State/Province]@row = "DC"), "EST", IF([Applicant State/Province]@row = "AK", "Alaska", IF([Applicant State/Province]@row = "HI", "Hawaii", ""))))))

  • Char Norman
    Char Norman ✭✭✭

    Thank you so much, Carson! My history is in excel and sometimes my brain goes back to it automatically.

    Appreciate your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!