Batch a list of ZIPs to time zones?

December 9, 2008 2:54 PM

Need to find time zones for an extensive list of U.S. ZIP codes I have in Excel. (Not for Spam.)

If there is a reasonable answer to this, you will save me hours/days of manual work. I have a list of thousands of contacts that I am simply trying to sort by time zone so I can divide up the day's work more effectively. My contact list is in Excel and the ZIP is in its own column. I have seen websites that return time zone info from zip code like this one. But, one-at-a-timing it is a daunting prospect.

Note: I have no scripting experience. I might be willing to purchase a tool to do this, but something tells me I probably shouldn't have to, especially since I will only do this task once. And, again, I am not spamming anyone. No emailing involved.

Ideas?

posted by skypieces to technology (6 comments total)

This was reasonably challenging to find, but here you go:

Grab the file "zipcode.zip" from http://mappinghacks.com/data/

You'll have to get it in Excel and set up a VLOOKUP. Should be easy enough.
posted by Perplexity at 3:07 PM on December 9, 2008 [4 favorites has favorites]

Internet Explorer is highly scriptable. You can do this with an Excel macro. Sorry, I don't have Excel on this computer so I can't test it. Right click on the tab that has the zip codes and click "View code". Paste this code in the window. Change the ZIP_COLUMN and OUTPUT_COLUMN constants to the appropriate column numbers (you must use numbers, not letters. A = 1, B = 2, and so forth). Go back to your spreadsheet and select the zip codes, then run the macro from Tools > Macros.
posted by zixyer at 4:56 PM on December 9, 2008

Well, that made relatively short work of what could have taken me days. Still a bit of formatting and such to be done (that table of zips seems to have a problem with zips starting with zero), but nothing I can't handle. Many thanks, Perplexity! Many thanks!
posted by skypieces at 4:57 PM on December 9, 2008

zixyer, I am going to try your solution as well. You posted as I was posting. I am eager to check this method out.
posted by skypieces at 5:00 PM on December 9, 2008

Sometimes if you're having problems VLOOKUPing data with Excel treating things that should be text as numbers, you can solve it like this: VLOOKUP(VALUE(A1),Sheet2!$A$1:$Z$9999,2,FALSE)

That is, run the VALUE function on your lookup cell before passing it to VLOOKUP.
posted by zixyer at 5:09 PM on December 9, 2008 [1 favorite has favorites]

I did run into some VLOOKUP difficulty with formatting. I found that making the cells referenced in the lookup absolute was essential. Everything slipped right in with ease. My ignorance of scripting held me up on that try. I pasted everything in but was getting compile errors. Thanks to you both for your help. I have just what I needed now and am off and running.
posted by skypieces at 7:22 PM on December 9, 2008