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.

[JonathanBowen]

Hi @Kate_Sopko - I wonder if this works for you? I’ve taken a slightly different approach.

Have two fields - Due Date (of type date, but no time element) and Due Time (of type Single Line Text with a default value of ‘08:00’.

When you create a new record the time element will default to 08:00 and you can override this on the occasions you need to.

Then use two additional fields to get these into the format you want:

Due Date/Time as STR uses the formula:

CONCATENATE(DATESTR({Due Date}), ' ', {Due Time})

and turns the due date and time fields into a single string.

DATETIME_PARSED then converts the string into a real date/time using:

DATETIME_PARSE({Due Date/Time as STR}, 'YYYY-MM-DD HH:mm')

Of course, maybe the last two columns are not required if you don’t need to further manipulate the dates and the Due Date and Due Time fields may be sufficient.

JB

[Kate_Sopko]

Hi Jonathan,

This is a great set of code, and I very much appreciate you sending it.

However, I think it doesn’t solve for allowing our users in different time zones to enter/view due dates in their local timezone rather than in GMT.

As far as I’ve been able to understand, this requires setting up the Due Date field with a time function and toggling off the “Use the same time zone (GMT) for all collaborators” function.

Am I missing something on this? Very well could be :slightly_smiling_face:

Thanks,

Kate

[JonathanBowen]

Hi Kate - I may have misunderstood your original comments - I read it to be that whether someone is in San Francisco or London or Sydney, they are only working with their local time, but reading it again are you saying that if you set the release date/time to, for example, 4th April 08:00 GMT you want someone in Sydney to understand this as 4th April 08:00 + 11 hours (or 4th April 19:00)?

I think this might be possible with the formula:

SET_TIMEZONE([date], [tz_identifier])

But for this to work I think you’d need to assign each task to a person and the person has an associated timezone.

JB

[JonathanBowen]

@Kate_Sopko - let me know if this was what you were thinking of and I’ll see if I can get it to work

JB

[Kate_Sopko]

Hi Jonathan,

What you’re describing could be a possible solution.

Every one of our reports (editorial flow for report production is what we’re tracking in this calendar) is assigned to at least one author, and those authors are all associated with time zones. When authors team up, it’s typically within the same time zone. Right now, Reports link to a table called
“Team” to assign authors to each report. The Team table is a simple table of author/editor names and contact info.

I had been thinking it might be possible to assign a time zone to each Team Member within the Team table itself, but from there, I am not certain how to make time zones associate with each report they’re authoring… I can send screenshots if that’s at all helpful.

Thank you so much!

Kate

[JonathanBowen]

Hmmm…well I thought this would work, but I’m getting errors. Posting where I’ve got to in case someone else can add to it.

My idea, which I think matches with your last comment @Kate_Sopko, was to have two tables - people and tasks (this is probably a lot more simplistic than you have, but hopefully the principles are the same).

In “people” we have a name and a timezone:

The timezone has to be one of the identifiers here:

Airtable - Support

Supported timezones for SET_TIMEZONE

The SET_TIMEZONE function will allow you to set the timezone for the data from a date type field. This is written in the form "SET_TIMEZONE(datetime, 'timezone identifier')," in which the...

Then in my tasks table, I assign a task to a person and do a lookup to the timezone:

I’ve then got various fields, most of which are described above, that allow you to set the GMT date and time, defaulting to 08:00, but overriding where required:

The last column, “local date time” is the “date time parsed” converted to the timezone of the assignee. Except that it isn’t working. If I use this formula for the local date time:

DATETIME_FORMAT(SET_TIMEZONE({Date Time Parsed}, {TZ}), 'M/D/YYYY HH:mm')

I get an error. I cannot get the TZ value into the formula dynamically. I know it works because if I hard-code this to ‘Australia/Sydney’ it works:

DATETIME_FORMAT(SET_TIMEZONE({Date Time Parsed}, 'Australia/Sydney'), 'M/D/YYYY HH:mm')

I’m guessing this is something to do with the SET_TIMEZONE requiring the timezone value to be quoted. But even if I quote it in its field or in the formula it doesn’t work. :frowning_face:

Here’s the link to my mockup:

Airtable

Timezones - Airtable

Explore the "Timezones" base on Airtable.

JB

[Justin_Barrett]

A lookup field doesn’t return a string. It returns an array, even when returning a single item. When referencing the contents of a Lookup field from a formula, Airtable defaults to leaving it as an array. To force it to be a string, you need to concatenate it with a string. Try this:

DATETIME_FORMAT(SET_TIMEZONE({Date Time Parsed}, '' & TZ), 'M/D/YYYY HH:mm')

[JonathanBowen]

Hi @Justin_Barrett - perfect! Just what I needed. A slight correction to your formula:

DATETIME_FORMAT(SET_TIMEZONE({Date Time Parsed}, '' & TZ), 'M/D/YYYY HH:mm')

(no curly braces around the '' & TZ )

Thanks!

JB

[Justin_Barrett]

Oops! I think I was more tired than I realized last night. I put the quotes inside your original curly braces surrounding TZ. I’ll fix my post now.