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