Consultant Tools Series: Coordinating Across Multiple Time Zones

Have you ever made a mistake trying to figure out times in different time zones? It happens to me all of the time--I forget whether a state is in Mountain or Central or Eastern. Or I accidentally add an hour instead of subtracting. Clearly not rocket science, but definitely an error prone process.

I've found two tools (so far) that help me keep track of time zones.

The first is FoxClocks, an Add-On for the Firefox web browser. This utility is essential for helping me coordinate with people in other time zones and countries, since I can quickly glance at my browser and know what time it is in any city or country around the world.



FoxClocks has served me well for several years now, but what is often challenging is coordinating a meeting with people in multiple time zones.

I recently had to coordinate a conference call with attendees from Alaska, California, Colorado, Ontario Canada, and Australia. Talk about mind bending.

After looking around for a solution, I thought of my good friend Excel. It appears that there are several ways to use Excel to help calculate the time across multiple time zones. I chose one of the simplest methods.

If you enter a date time value in a cell, you can then create a formula to add or subtract hours from that cell to produce a time zone offset.

So if your tentative meeting time in cell A1 is 3/3/2010 11:00 am Pacific time, in cell B1 you can use the formula =A$1$ + "3:00" to get Eastern time. There are several other much fancier techniques to calculate time zones in Excel, but this approach was plenty adequate for my needs.

Here is a sample of what my spreadsheet looks like:



The green cells are the manually entered values, allowing me to enter the desired meeting time in both Mountain time and Alaska time and see how it affected the times in all of the other time zones.

And these are the formulas behind the values:



With my simple approach, I had to manually adjust the offsets that were used on row 5 and row 7, but it was simple enough to do.

The downside to my low-tech approach is that it doesn't automatically account for daylight saving time. I'm assuming there are ways to automatically handle DST, so maybe I'll deal with that when the time change rolls around.

Happy teleconferencing!