Event timetable - across multiple timezones

Hi there,

  • We’re doing a two-day world record-breaking “zoomathon” attempt to aid the charity Hands Across the Water helping underprivileged kids in Thailand find homes, get access to technology and education.
  • We’re using our Glide App for the timetable (and later on to store the replays). We have over 4000 people registered and we’re closing in on our target of $150k raised.
  • As event hosts, we are located in Australia, but there are hosts and guests from around the world - New Zealand, Nth America, the UK and SE Asia (Thailand, of course!). Think Live Aid, but instead of musicians, it’s real estate agents trying to do good and build homes.
  • The event program in our Glide App - images attached - shows AEDST - but there are even 3 timezones in Australia at the moment. Because the event is over two days (and starts tomorrow) there is probably no point in putting every session into Addevent
  • Is there a “Construct URL” type way to convert timezones around the world on the fly? Or is the only way to add a heap of timezone columns in my sheet and then get the user to select their closest timezone?
    Any hints will be gratefully appreciated while I work on my opening remarks for tomorrow (nothing like putting pressure on myself!!)



1 Like

This may give you an idea.

2 Likes

Thanks @SantiagoPerez , I did look at that yesterday - but that all seemed way too much to decipher in a couple of hours and felt extremely complex for the job. And without seeing what dates/times were being returned I didn’t feel confident that everyone would get the right time and I don’t want anyone missing out!! (or the support calls from frantic users) I’m really looking for something like - has someone integrated Glide into something like timeanddate.com using a construct URL of some kind - or was thinking about hard coding columns with specific timezones myself (not ideal and time consuming)

1 Like

I would seriously take another look at the solution @SantiagoPerez pointed out. You can copy the app if you haven’t already. It might seem overwhelming, but there is probably more in there than you need. If you look at the data table, pay attention to the first 4 columns. A lot of the other columns are just to show the offset value a certain way on the screen, values to control the clock on the screen, and just some other random stuff I was testing. The first 4 columns are the most important.

But, I think you could get away with a similar but much simpler approach. If all of your event times are going to be based on your local time of AEDST, then I would create a template column that joins the event date and either AEDT or +11. You end up with something like this:
image

When you set that template column as the source of your date setting for the calendar list, it does the automatic conversion to your local timezone. So your event that starts at 9AM on the 23rd, would start at 10PM on the 22nd GMT time, and for me it works out to 4PM on the 22nd for my personal local time since I’m 6 hours behind GMT time.
image

That way, each user will see the correct time because you used the template column to specify that the event time is in your specific AEDT or +11 timezone instead of an ambiguous datetime.

I’ve updated the example @SantiagoPerez shared, so if you copy it again, you will see the new columns at the end.

3 Likes

That’s awesome :+1:

2 Likes

That’s even more awesome. I thought we can only use a sheet column and let Glide convert it using a date/time column.

3 Likes

You never know we might be encouraging the next generation of no-code glide awesomeness in some way…!! :pray:t2:

1 Like

OK @Jeff_Hager you win… I’m going back in there. Wish me luck!

2 Likes

When I started writing this out, I was thinking of converting everything to GMT time first, but then I remembered what you had mentioned in the past about setting the timezone directly to the datetime. That ended up being a much quicker method.

I think the same way that a date column can interpret almost anything as a date (tomorrow, 3 months from now, etc.), the calendar interprets dates the same way. You can feed it text and it will figure it out magically.

The only downside is that you can’t work with the template column as a date. Also, can’t apply any filtering against that template. You can sometimes pull off formatting by using it in a math column, but you have to first use a real date column to get the formatting options to show up, then switch it to the template column and the formatting options should stick. Also, you can’t do any date math on the template. I think you would have to first go through the work to figure out the offset number, and then do the math against the original date using that offset. Doable but with some additional work if you still need the resulting date to be treated as a date everywhere.

3 Likes