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

[Kate_Sopko]

Hi, I am working on an Airtable base that serves as an editorial calendar for analysts operating around the globe. I have been struggling to figure out the best way to configure due date fields so they are meaningful to everyone on our team.

My current experiment is to disable “use the same time zone (GMT) for all collaborators” in the due date field, which allows every user to enter and view due dates in their local time. However, this requires formatting the due date as a date/time field, in which the time defaults to midnight. Our people will rarely enter a due date time, and I would like to be able to default a non-entry to 8:00 AM, so that it is more clearly aligned with working hours. Note: there are infrequent occasions when we do need to enter an actual release time, to be understood in the time zone of the local user.)

I have been trying an IF formula that references the Due Date field, but am having trouble configuring it correctly. If anyone has experience building successful IF formulas that reference Time in a Date field, I’d be much obliged for by advice you can give.

These were some things I tried. The due date field is named “Release Date Local Time”.

IF({Release Date Local Time = “12:00am”), “8:00am”, “Time entered”)
IF({Release Date Local Time} Time = “12:00am”), “8:00am”, “Time entered”)
IF({Release Date Local Time} TIME = “12:00am”), “8:00am”, “TIME entered”)

All these and a lot of other iterations resulted in error messages.

[Kate_Sopko]

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’)