Using an IF function to manage due dates across multiple time zones

#21

In case anyone is trying something similar, here’s what we figured out to make an editorial calendar work amongst various time zones. Note: the places where my organization does the most releases are NY (GMT-5) and Sydney (GMT+10).

In our Team Table, I added a timezone (‘TZ’) single line text field for all team members, using Airtable formatting standards (ie: ‘America/New_York’ or ‘Australia/Sydney’).

In our Reports Table, I change our format to have one lead author on every report, linked to the Team Table through a link to the Team Table that doesn’t allow linking to multiple records. This field is called ‘TZ of Author’.

We then have several release date fields to get to one that translates releases into local time:

Release in GMT- a Date field in Friendly format (which translates best among users who format number dates differently.)

Release Time- I entered a default of 21:00, or 9:00 PM GMT, which makes the translated time fall within a workday for New York (5:00 PM) and Sydney (7:00 AM). This field is hidden from users. I found that if you use “09:00 PM” this does not work in a formula field.

Deadline String formula- This turns the Release Date and Time fields into a single string. Hidden field.

CONCATENATE(DATESTR({Release, in GMT}), ’ ', {Release Time})

Deadline Parsed- This converts the string into a real date/time. Hidden field.

DATETIME_PARSE({Deadline String formula}, ‘YYYY-MM-DD HH:mm’)

Release in Local TZ formula- Converts Deadline Parsed to Timezone of local author. Hidden.

DATETIME_FORMAT(SET_TIMEZONE({Deadline Parsed}, ‘’ & {TZ of Author}), ‘M/D/YYYY HH:mm’)

Release in Local Time- Converts Local Release Date into Friendly Format. Visible.

DATETIME_FORMAT({Release Date in Local TZ formula}, ‘LLL’)

Date Local Formula- Converts Release in Local Time to a Date Format the Calendar View in Airtable can pull from. This allowed us to show our release dates in the proper local times where our reports would be coming out around the world. Hidden, but our Calendar View populates from this field.

DATETIME_PARSE({Release Date in Local TZ formula}, ‘M/D/YYYY H:mm’)

2 Likes