Formula for column converting zip code to time zone

I have a column where our form data populates a zip code. I created another column for time zone where I would like the zip code column to populate the time zone column.

I've worked on a formula for the time zone column and can't figure out what is not working. Any ideas would be helpful.

=IF((((((“97001:97920”,[[Zip / Postal Code]@row = "98001:99403", [Zip / Postal Code]@row = "97001:97920",[Zip / Postal Code]@row = "90001:96162",[Zip / Postal Code]@row = "88901:89883"), "PST",IF(OR([Zip / Postal Code]@row = "59001:59937",[Zip / Postal Code]@row = "83201:83877",[Zip / Postal Code]@row = "82001:83414",[Zip / Postal Code]@row = "84001:84791",[Zip / Postal Code]@row = "80001:81658",[Zip / Postal Code]@row = "85001:86556",[Zip / Postal Code]@row = "87001:88439"), "MST",IF(OR([Zip / Postal Code]@row = "58001:58856",[Zip / Postal Code]@row = "57001:57799",[Zip / Postal Code]@row = "68001:69367",[Zip / Postal Code]@row = "66002:67954",[Zip / Postal Code]@row = "73001:74966",[Zip / Postal Code]@row = "73301:88595",[Zip / Postal Code]@row = "55001:56763",[Zip / Postal Code]@row = "50001:52809",[Zip / Postal Code]@row = "59001:59937",[Zip / Postal Code]@row = "71601:72959",[Zip / Postal Code]@row = "70001:71497",[Zip / Postal Code]@row = "53001:54990",[Zip / Postal Code]@row = "60001:62999",[Zip / Postal Code]@row = "37010:38589",[Zip / Postal Code]@row = "63001:72643",[Zip / Postal Code]@row = "35004:36925"), "CST",IF(OR([Zip / Postal Code]@row = "48001:49971",[Zip / Postal Code]@row = "46001:47997",[Zip / Postal Code]@row = "43001:45999",[Zip / Postal Code]@row = "15001:19640",[Zip / Postal Code]@row = "00501:14925",[Zip / Postal Code]@row = "05001:05907",[Zip / Postal Code]@row = "03901:04992",[Zip / Postal Code]@row = "03031:03897",[Zip / Postal Code]@row = "01001:05544",[Zip / Postal Code]@row = "02801:02940",[Zip / Postal Code]@row = "06001:06928",[Zip / Postal Code]@row = "40003:42788",[Zip / Postal Code]@row = "07001:08989",[Zip / Postal Code]@row = "19701:19980",[Zip / Postal Code]@row = "20588:21930",[Zip / Postal Code]@row = "24701:26886",[Zip / Postal Code]@row = "20101:24658",[Zip / Postal Code]@row = "27006:28909",[Zip / Postal Code]@row = "29001:29945",[Zip / Postal Code]@row = "30002:39901",[Zip / Postal Code]@row = "32003:34997",[Zip / Postal Code]@row = "20001:20599"), "EST",IF([Zip / Postal Code]@row = "99501:99950","Alaska", IF([Zip / Postal Code]@row = "96701:96898","Hawaii", ""))))))

Answers

  • Ryan Kramer
    Ryan Kramer ✭✭✭✭✭

    So a different approach that I would recommend. Add all of these zip codes to another sheet and do a vlookup given a zip code.

    Ryan

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

  • Char Norman
    Char Norman ✭✭✭

    The worksheet where the forms enter the zip code is continually being updated. The vlookup won't continue to update the additional rows so I would have to continually be updating. Make sense?

  • Ryan Kramer
    Ryan Kramer ✭✭✭✭✭

    Not sure if I understand. The zip codes would likely be a reference that wouldn't change and I think you can just make the column that references it a formula column so that on any record in that sheet, it would use the reference accordingly.

    Maybe I am missing something.

    Ryan

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!