RSCON11 Schedule - Making It Multi-Timezone

When I first saw the Google spreadsheet schedule for the RSCON11 Virtual Conference it looked like this :

The times of each presentation were given for eight timezones but mine here in Sri Lanka (GMT+5:30) wasn't listed due to space limitations. I could have clicked on the associated links to check the times but that would have been a nuisance to do - far better to have one sheet with my local times visible. I also realised others would be having the same inconvenience. Besides, it being a spreadsheet, it was crying out to be programmed! I decided to make a version where you could enter your timezone and have the spreadsheet calculate your times.  The end result looked like this :

The above shows the schedule for Perth, at GMT+8:00. The cream colour indicates the day change but unfortunately Google in its wisdom has decided that midnight, with a valid time of 00:00, should not be displayed. Now, I realise this is far from perfect but the fact that you could put in any timezone you wanted seemed like an improvement to me, and the generous organisers of #RSCON11 supported it as an alternative method of schedule presentation.

I gave some instructions for downloading this spreadsheet a little before the conference started.

 I have been asked how I programmed it. It is actually quite straight forward. Firstly the cells containing dates and times should all be formatted withe the Date time format :

It's unfortunate that the date format has to be the crazy mm/dd/yyyy  but that is all Google makes available, unless you want to get all complicated, and I didn't! At least the time of day is given in the 24-hour clock which is great because it doesn't have the ambiguity of the 12-hour scheme.

As well as formatting these cells you should also click the text colour or background colour icons and select "change with rules". Here I've changed the font colour to dark blue and the background to cream when the cell contains the string "1/9/2011" :

Next you need to format the two cells with times in them, using the Time format. What those are for will become obvious in a second :

Now we get to the point where we need to add formulas. Columns A and B contain times in the PST time zone. These lag GMT by 8 hours so, to get times in GMT, you need to add 8:00. That's the value contained in cell I4.  To get the times in your local timezone you need to add your offset from GMT. That is the value contained in cell C4.
So, for example, if the time is 07:00 PST then adding 8:00 gives 15:00 GMT. And if you're in Sri Lanka or India (which have an offset from GMT of +5:30) then adding 5:30 gives a local time of 20:30.
The time in cell I4 doesn't really need to be visible because it's a constant but if it was hidden then a user might think the cell was empty and write something else on top of it.

The formulas in the cells are as follows:

C6 is =A6+C4+I4

D6 is =B6+C4+I4
...and so on.

If you're going to copy and paste the formulas (much easier than typing each in individually!) then the C4 and I4 references need to be made absolute rather than relative to the cell you're putting the formula in. That means that you need to put a $ in front of the column and row reference. The formulas end up looking like this:

C6 is =A6+$C$4+$I$4

D6 is =B6+$C$4+$I$4

C8 is =A8+$C$4+$I$4

D8 is =B8+$C$4+$I$4

C9 is =A9+$C$4+$I$4

D9 is =B9+$C$4+$I$4

...and so on.

And that's all there is to it. Perhaps that'll help you if you ever need a multi-timezone conference schedule.

I have an idea how to improve this in the future. It entails having a master timezone schedule on sheet 1 and then copying the cells onto further sheets and doing the necessary time adjustments. It will mean you won't even have to enter your timezone, you'll be able to just point directly at the corresponding sheet. That's the idea anyway...

---

If you happen to know how to make midnight (00:00) visible, or if you know how I can add in a constant time directly into the formulas, please let me know!