Are you planning an event with a global audience? Let the world know at what time your webcast, webinar, or chat will begin or when to tune into an upcoming premiere, concert, or game. The timeanddate.com Event Time Announcer converts the time of your event to the corresponding local times around the world.
As someone who organises and delivers live online training, I make extensive use of the free Event Time Announcer at http://www.timeanddate.com/worldclock/fixedform.html. The page contains a simple form where you fill in your event name, date, start time and choose a location/timezone.
For example:
- Event: Excel Training
- Date: 6th September 2013
- Time: 8:00PM
- Timezone: GMT
When you click the Show Result button, a URL is generated showing a list of locations around the world together with the corresponding times. For the above Excel Training event, the URL would be:
http://www.timeanddate.com/worldclock/fixedtime.html?msg=Excel+Training&iso=20130906T2000
My fiancee and I organise and deliver weekly live online training events. We have an Excel spreadsheet containing the following headings:
- Event Title
- Date
- Time (GMT) – makes sense for us because we are UK based
- TimeAndDate URL – we have an international audience so a link to the generated URL is put in the event description on the websites where we advertise and promote our training sessions
The Event Time Announcer URL always begins with http://www.timeanddate.com/worldclock/fixedtime.html?msg=
This is followed by the name of the event. However, because a URL cannot contain spaces, the Event Time Announcer system replaces spaces with a plus symbol (+)
This is followed by the static text &iso=
This is followed by the event date in the format YYYYMMDD
This is followed by the letter T (for Time)
This is followed by the start time in 24hour format (e.g. 1900 or 2030)
Due to the large number of events that we organise, it would be time-consuming to go to timeanddate.com, type in the event title, date and time for each event, click Show Result, copy the URL and paste it into the TimeAndDate URL column in the spreadsheet. Therefore I automated this process with a formula. This is the formula I entered into D2 (and copied down column D for each event):
=”http://www.timeanddate.com/worldclock/fixedtime.html?msg=” & SUBSTITUTE(A2,” “,”+”) & “&iso=” & TEXT(B2,”yyyymmdd”) & “T” & C2
The ampersand (& sign) is used to combine together static text elements, results of functions and cell values.
The first part of the formula generates a static text string http://www.timeanddate.com/worldclock/fixedtime.html?msg=
The second part uses the SUBSTITUTE function to replace every space in A2 (the cell containing the Event Title) with a + symbol
The third part generates a static text string &iso=
The fourth part converts the date in B2 to an appropriately formatted text string
The fifth part generates the static text T
The final part captures that time from C2
