Convert two-letter state to timezone in excel / google sheets

Get your formulas here:

[rdytrvl]

Thanks, the Google Sheets formula works perfectly!

[pcofranc]

Big thanks I listed the original version and and modified version that shows the current time for each state (F9) to refresh update the time:



This version adds two features: 1. Displays the current time in each state (I did this in CA so CA is the time and I add 1 hr for each time zone going back east) and subtract 1 hr for Alaska, 2 hrs for Hawaii. 2. if the State cell is blank a dash will be displayed "-" which could be modified to " " for just blank space.

=IF(NOT(a1<>""), "-", IF(ISNUMBER(SEARCH(a1, "WA, OR, CA, NV")), TEXT(NOW()+TIME(0, 0, 0), "hh:mm:ss AM/PM"), IF(ISNUMBER(SEARCH(a1, "MT, ID, WY, UT, CO, AZ, NM")), TEXT(NOW()+TIME(1, 0, 0), "hh:mm:ss AM/PM"), IF(ISNUMBER(SEARCH(a1, "ND, SD, NE, KS, OK, TX, MN, IA, MO, AR, LA, WI, IL, TN, MS, AL")), TEXT(NOW()+TIME(2, 0, 0), "hh:mm:ss AM/PM"), 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")), TEXT(NOW()+TIME(3, 0, 0), "hh:mm:ss AM/PM"), IF(ISNUMBER(SEARCH(a1, "AK")), TEXT(NOW()-TIME(1, 0, 0), "hh:mm:ss AM/PM"), IF(ISNUMBER(SEARCH(a1, "HI")), TEXT(NOW()-TIME(2, 0, 0), "hh:mm:ss

Note: if the data is invalid then "FALSE" will be displayed. Also, to modify the formula paste it into notepad and perform a search for cell A1 and replace it with the desired cell". Also, this is to be entered as a single line in excel not multiple lines. If you see multiple lines in notepad click Format and uncheck "Word Wrap". Took me hours to get the syntax right and not miss a comma, (parenthesis), etc.

[mahmoudgoma]

i need that in excel sheet


or pls . upload file excel sheet for auto convert phone number to "Pacific", "Mountain", "Central"