Formula for state abbreviation and zip codes into time zone

Char Norman

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!

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