Time Zones: Today / Tomorrow Time Zone Conversion Chart

DISCLAIMER: This is not as scary as it looks, especially if you like spreadsheeting! But, only beginner level spreadsheet skills needed!


Some times the hardest thing can be doing simple timezone conversions! Typically I tackle this in two ways. One is a simple conversion that I ALWAYS do, and the other is more complex for specific types of trips or time zones. This article will cover the simple version.

For every trip outside of the United States, I make what I call a “Today / Tomorrow Time Zone Conversion Chart” using a spreadsheet program (I prefer Google Sheets because I can have it on the go) and timeanddate.com.

Example chart used on a trip block to Spain and Portugal with day trips to Morocco and Gibraltar, a layover at London Heathrow… and many daylight savings to deal with.

General Format

The general format of the spreadsheet is the same every time. Since our work time zone (Pacific) is the most important, it always goes on the far left. I fill in everything from there. I include every time zone we will visit in each trip block (what we call a US -> other places -> US loop). I also will include if we will encounter any Daylight Savings (in or out) both in the US and in other places (which obviously never match up with US time changes - that would be too easy!).

Headers

Row 1: UTC Time Zone

It is actually pretty common for countries to have their own names for the same UTC offset, especially when dealing with Daylight Savings Time. For example, Hong Kong Time (HKT) and Malaysian Time (MYT) are both UTC +8. I prefer to list HKT and MYT in their own columns with UTC +8 at the top of both, letting me know quickly they are the same actual time. Depending on how many time zones we are passing in a trip block, if things get full and I can’t print it all on one page, I can merge things together pretty quick by the UTC offset.

Row 2: Time Zone Name

This is the actual location-based name. I like both the long, spelled out version and the three-letter abbreviation. For example: Pacific Standard Time (PST), Hong Kong Time (HKT), Central European Time (CET), Central European Summer Time (CEST). If I do end up lumping everything into the same UTC offset, I will usually just put the time zone name we will be in the longest and indicate the rest by the location section (keep reading).

Row 3: Actual Locations Visited

In this row, I list the specific places we will be. This could be by country, by city, by state, etc. - whatever makes the most sense for the trip. I will also list ALL of them even if we leave the timezone and come back to it later in the trip. For example in the above chart, for Central European Time (CET), I listed “Spain, Gibraltar.” But, since we were still in Gibraltar and went back to Spain after the time change, for Central European Summer Time (CEST) I listed “Spain, Gibraltar” again.

Body of Chart

This is where the magic happens!!! I make an hourly list in each time zone. You can use am / pm or you can use 24-hr, whichever you prefer. I used to only use am / pm but now I will use a little of both.

For this section, I’m going to walk through a new example. It is from our South Asia to South America trip block.



Column 1: Your home / default / most important timezone

The first cell will be 12:00 am and fill in every hour until 11:00 pm, probably. The chart at the top of the article (Spain / Portugal trip block) starts with 11:00 PM because we began the trip block in Pacific Standard Time but very quickly switched to Pacific Daylight Time. To make it easier to read for the majority of the trip, I based all the conversions on PDT so 12:00 am could be at the top. However, most of them start with 12:00 am or 00:00, as in the immediate example above (and the one I will use going forward).

Columns 2+: All the time zones in your trip block

This is where timeanddate.com comes in! I’ll break this out into steps since this is where it gets tricky. I’m sure the order of the time zones makes no sense at all - allow me to explain! I’ve got Pacific and Eastern on the end because they are our work and base time zones. Then, the rest are listed in the order we visited them, but it happens to line up West -> East from our US departure city.

Experience Tip: When I first started tinkering with the chart, I used to put all the time zones in UTC offset order. In the end, it was hard to keep track of how our work time zone mapped to everything else (read: set our local work hours everywhere). Now, I try to put the time zones in the order in which we will encounter them. Sometimes we go back and forth, so when needed, I will number the order we use them at the very top, as seen in the example chart at the top of the article. But, I will also put the time zones in West -> East order of our trip block, AFTER our work time zone of course. So… do what makes sense to you and your trip needs!

Now, let’s fill in the chart!

Step 1 and Step 2

Step 1: Identify current time / hour block

Identify the general HOUR you are currently working in with your work time zone. For example, let’s say I’m sitting down at 11:13 am, thus the 11:00 am hour block. This will be my starting row in each column to complete the rest of the data.

Step 2: Fill in current hour block across chart

Next, I will look up the time in each of the cities we will visit on timeanddate.com and identify the hour block equivalent to 11:00 am in each location. Even if I’m pretty sure of a conversion, I will still check it…. remember, time zone math is critical to do correctly!

Step 3: Auto-fill rest of chart

Once you are confident at what hour it currently is in each time zone, now you can either use spreadsheet magic to drag and automatically fill in the boxes, or you can manually type everything in.

Experience Tip: If you are handy with excel / spreadsheet programs, you can save time and drag out the pattern in each column. If not, the three-second version is this:

  • Type a time in one cell (may be at the top of the list, may not be)

  • Click the cell so the cell is highlighted and the cursor square shows up at the bottom corner

  • Click and drag that outline down as far as you need it to go. If you need to fill up / above your cell, it will work too. Note that you can only go one direction at a time (up or down), but you can select the whole row and do multiple columns (up or down) at the same time.

    Sidebar: I typically use hourly increments. If you need more granularity, the click and drag option will still work. You need to type the first two times (for example, __:00 and __:30 - or whatever increment, really, just as long as you have the first two to set the pattern), select BOTH of those cells, then drag.

Step 4

Step 4: Color-code today / tomorrow changes

Time to mark the “Today / Tomorrow” component of this chart! Part of the challenge with time zone math is if you are on the same date or tomorrow’s date from your work time zone. While the date change is indicated with PM -> AM, color makes things easier to see. I use a light pink because most of the time it will still show up with a black-and-white printer but won’t be so dark you can’t see the times.

Identify the day change 11:00 pm -> 12:00 am. Anything from 12:00 am to the end of the chart gets colored to indicate when your local time zone switches into a new date. It may not seem super useful on time zones near your work time zone, but once you have more than maybe 6-8 hours different - trust me it is helpful.

But what about the yellow part for French Polynesia?? Well……. occasionally (and we’ve only had it occur twice from 2014 to 2019 worth of traveling) you need a yesterday marker. When you’re hopping over the International Dateline during a trip (and not just at the very beginning or end), you will have a backwards time change. You don’t want to use the same color at your PM -> AM switch because your PM times are actually the day before your “current day” white color. I chose a light yellow so it will still print fine in black-and-white, but also it catches my eye that it is different.

Step 5

Step 5: Indicate your work hours

The last critical piece is marking your work hours. For us, it is 9:00 am - 5:00 pm Pacific time. I usually just use a dotted line marker across all the time zones to easily map what our work hours will be in the local places.

Step 6: Other formatting goodness

Now you can format your chart until your heart is content! Personally, I like the header boxes a lighter grey (printability). I also prefer to bold our work time zone, indicating its importance! Finally, I don’t like to outline each cell with the regular black outline, just the larger column chunks. You can add the individual cell outline with the lighter grey tone, but that is a lot of work when Google Sheets will do it for you. Google Sheets’ default is to show the grid lines when you print so save some time. You can also turn this feature off in Google Sheets in the print settings section under "Format” when you go to print if you don’t like the grid lines.

Summary

So, yeah, this means we work 8:00 PM - 4:00 am in Doha or 2:00 am - 10:00 am in Tokyo or 2:00 pm - 10:00 pm in Santiago But… this is the secret sauce to making TRW with set hours successful! This weird, ridiculous timekeeping is what allows you to still work…. and then see the world at the same time!

Resources

  • timeanddate.com

  • sheets.google.doc