Time Zone of Team


#1

I am looking to have applicants for a project from around the world. When they apply using the Airtable form, I want them to choose their time zone from a drop down menu.

I can easily create a drop down menu in Airtable, but I would have to manually enter a list of cities and time zones.

Is there a faster way to create this?

Thanks,


#2

I don’t know of a way that’s native to Airtable. But if you have your list of timezones in an excel or spreadsheet file, you could create a new table in your base by pasting the contents of the list. Then you could use a linked field in the application table to select the desired timezone in the new table, just as if it were a single select field. And if the applicant knew what he or she were doing, they could start typing in a few characters (like “Eastern”) and Airtable dynamically filters on the text.

If you don’t have your list of timezones, good luck. Your question sent me in search of one and there’s a lot of complexity around it. See for example:


https://data.iana.org/time-zones/tz-link.html

JB


#3

Try this:

  1. Go to this page.

  2. Mark and copy the list of timezone identifiers.

  3. Create a new single-line text field (or a new table and, within it, a single-line text field) in your base; call it {TZ}. (If you’re still in the process of creating your base, it’s easiest simply to define a new field in the table where your single select field will eventually belong. If you’re working on an existing base, it’s easiest to create the single select inside a new table and afterwards use this technique to move it to where it belongs.)

  4. Select the cell containing the new text field in Row 1.

  5. Press Ctrl-V to paste the timezone list.

  6. Airtable will ask if you want to add 330-some records to the base; say ‘yes.’

  7. You now have 341 records, each containing a single timezone identifier within single quotes; the first 340 also have a comma appended. Scroll down to Row 341, select the text cell (‘Pacific/Wallis’), press Enter, and add a comma to the end of the entry.

  8. Create a new formula field called {Timezone}.

    1. If you want your single select items to include the single quotes, use this formula:
      LEFT(TZ,LEN(TZ)-1)
    2. If you want to strip both the quotes and trailing comma, use this formula:
      RIGHT(LEFT(TZ,LEN(TZ)-2),LEN(TZ)-3)
  9. Right-click on {TZ} and select 'Customize field type'. Change the field type from 'Formula' to 'Single select'. (It’s probably best to de-select 'Colored options', as well.)

You are now the proud owner of a 341-item single-select field called {Timezone} containing all Airtable-acceptable timezone identifiers in far less time that it took me to describe the process. Delete the now-unnecessary records and proceed with defining your base, or move the field to the appropriate table, delete your ‘scratch’ table, and carry on Airtabling.


Setting current time in different timezones
#4

Brilliant! That worked like a charm.

Thank you for taking the time to help with this.


#5

Great! Sometimes I look back on what I’ve written and can’t believe anyone manages to follow it… :slight_smile:


#6

Your instructions worked well. The only glitch I ran into was you told me to name the field {TZ} with brackets and I followed that literally and named the field {TZ} with the brackets included.

So the formula failed the first tie as the formula was using TZ.

Once I renamed the field to TZ, then the formula worked.

Are you a programmer? Do you do Airtable and other consulting work?


#7

I’ve replied via PM here…