My task was to import data of around 12,000 records into our database. It’s not a difficult task, right? Well, the only hiccup was to detect and input the timezone for each record. You don’t want to manually insert the timezone for 12000 records and so you may go for some easier way. Let me tell you the easier way.
The time-consuming way is to use an API where you will supply the state name as input and get relevant timezone. 12,000 records x 12000 api requests = N minutes. No no !!!.
Well, how about simple Excel formulae for it?
You guessed it. Here is the solution for the same.
=if(ISNUMBER(SEARCH(L2,"WA,OR,CA,NV")),"Pacific Time (US & Canada)",if(ISNUMBER(SEARCH(L2,"MT,ID,WY,UT,CO,AZ,NM")),"Mountain Time (US & Canada)",if(ISNUMBER(SEARCH(L2,"ND,SD,NE,KS,OK,TX,MN,IA,MO,AR,LA,WI,IL,TN,MS,AL")),"Central Time (US & Canada)",if(ISNUMBER(SEARCH(L2,"MI,IN,OH,PA,NY,VT,ME,NH,MA,RI,CT,KY,NJ,DE,MD,WV,VA,NC,SC,GA,FL,DC")),"Eastern Time (US & Canada)",if(ISNUMBER(SEARCH(L2,"AK")),"Alaska",if(ISNUMBER(SEARCH(L2,"HI")),"Hawaii",""))))))If you want to change the Timezone format like “PDT” instead of “Pacific Time (US & Canada)”, You can do so as depicted below:
=if(ISNUMBER(SEARCH(L2,"WA,OR,CA,NV")),"PDT"I hope you will find this simple trick useful.
