Time Zone Formula in Excel

Options
Melissa S Graves
Melissa S Graves
Ninth Anniversary 500 Likes 500 Comments Photogenic
in Blackbaud Raiser's Edge NXT®: General Discussion
Looking to my wise friends out there for help in excel.


I am looking to create a formula in Excel based on US State to have the formula display the time zone the constituent is in. My brain is fried and all I can think to do is 50 imbedded if then statements. Anyone have other ideas?


Cross posting - sorry if you see this more than once out there in the interwebs.
Tagged:

Comments

  • Joshua Bekerman bCLO bCRE
    Joshua Bekerman bCLO bCRE
    Tenth Anniversary 500 Likes 500 Comments Name Dropper
    I would use VLOOKUP along with a separate spreadsheet that maps states to timezones, although some states have more than one timezone.
  • JoAnn Strommen
    JoAnn Strommen Community All-Star
    Ancient Membership 2,500 Likes 2500 Comments Photogenic
    Remember, time zone lines don't always follow state borders. In South Dakota it primarily follows the north/south river thru the middle of the state. Half state is in Central time zone, half in Mountain.  LOL 


    North Dakota's split also.


    Josh's idea sounds like a good way to deal with that.
  • Denise Covington
    Denise Covington
    Ancient Membership 100 Comments 100 Likes Photogenic
    How about using an online time zone converter?  This one is city to city...


    https://www.timeanddate.com/worldclock/converter.html
  • Blake Moody 3
    Blake Moody 3
    Ninth Anniversary 10 Comments Photogenic 5 Likes

    Melissa Graves:

    Looking to my wise friends out there for help in excel.


    I am looking to create a formula in Excel based on US State to have the formula display the time zone the constituent is in. My brain is fried and all I can think to do is 50 imbedded if then statements. Anyone have other ideas?

    I concur with Josh. A vlookup is the way to go so you can easily manage and visualize the timezones and locations you are using. I always put the extra data that fuels my vlookup in another tab. (You can hide that tab so it won't be in your way once you are set.)


    You'll have to go more granular than state, and I'll bet city won't be 100% accurate enough either. So perhaps zip? There are options to download this data online. I saw something like https://timezonedb.com/download has a CSVs file you can download and then load into your spreadsheet in tabs just devoted to the timezone and zip listing info. Then you can use the Vlookup formula to pass the constituents zip code to lookup the row with it's corresponding time zone in downloaded zip data in your new tabs.


    Fun project, I'll say!


    Blake

  • Melissa S Graves
    Melissa S Graves
    Ninth Anniversary 500 Likes 500 Comments Photogenic
    I was able to find a source for time zone by Zip so I am using VLookup! Thanks!
  • Heather MacKenzie
    Heather MacKenzie
    Tenth Anniversary 1,000 Likes 1000 Comments Photogenic
    I just really like that Blake considers this a fun project!  You're are my kind of people, and I'm grateful I'm not alone.

Categories