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

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

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

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

@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

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

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:

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:

JB

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

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

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.

Many thanks to you both, and an apology for a delayed response. Our office is in the middle of a move, so have not had time to dig back into this problem. I will try this out today and let you know if it works. Very much appreciate your help here!

Virus-free. www.avast.com

Hello Jonathan and Justin,

Can’t thank you enough for you help on thinking this through! I am very close to making this work, but ran into issues right at the end. Here’s what I did with your excellent advice:

In our Team Table, I entered the Timezone (in Airtable format) for each of our authors.

In our Reports Table, I:

• changed the Release Date field to not include a time.

• set up a Due Time field with the default at 08:00 AM like you had it. Worked great.

• followed the instructions on CONCATENATE(DATESTR and DATETIME_PARSED, which both worked great.

• created a Lookup field that looks up the Timezones of Authors associated with each Report. Worked.

• I then created an ARRAYUNIQUE on the Timezone Lookup field to eliminate showing several time zones when there are several authors.

• This worked, with the exception of 2 Reports where 2 authors are working in different time zones. I think I will deal with this by having a lead author and co-authors in separate fields when authors are working in different geographies.

• When I got to translating the Deadline into the Timezone of the author, I am getting an error message (which is not surprising, as I set this up a little differently than you did.)

Here’s what I tried. (I did this and a couple other iterations of it. None worked.)

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

If you have any insight, I’m all ears.

Ideally, if that time zone translation can work, I’d like to then format the deadline into ‘Friendly’ format, as that works for all our users’ date format preferences.

I was able to successfully apply the following to the Date Time Parsed field, and am hoping to be able to apply it to the deadline translated into timezones, if any of us can figure out how to make that work.

DATETIME_FORMAT({Due Date and Time}, ‘LLL’)

Thanks again for all your help!

Virus-free. www.avast.com

@Kate_Sopko, you seem to have repeated that little inadvertent mistake from above where you have your concatenation inside curly braces.

Try this:

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

Yep, curly braces :slightly_smiling_face:

Also to note (I wasn’t clear from your last comment how you finally had it set up) that the SET_TIMEZONE formula will only work on a single timezone identifier - if you pass through multiple timezones (with the unique array formula) SET TIMEZONE doesn’t work (it doesn’t error either as far as I can see, just doesn’t work).

JB

@Kate_Sopko - your friendly date formula seemed to work fine for me:

DATETIME_FORMAT({Due Date and Time}, 'LLL')

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

Yes, I happened to see that after the fact! Thank you, it ended up working well!

Virus-free. www.avast.com

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

Thanks, yes, I decided to go revise our structure to have just one lead author on every report in order to eliminate that issue. It’s working great, and I couldn’t have done it without your help!

Virus-free. www.avast.com

Yeah, it did the trick! I am having one last problem here. We use these due dates in a Calendar View, and I’d like to have the Die Dates as translated into the local time zone be what shows up on the Calendar rather than the GMT time which is often a different day for many of our users.

I think this can be done through DATE_TIME PARSE?

I tried these formulas

DATETIME_PARSE({Release in Local Time}, ‘YYYY-MM-DD’)

and

DATETIME_PARSE({Release in Local Time}, ‘YYYY-MM-DD HH:mm’)

and got error messages with both.

Sorry for so many questions!

Virus-free. www.avast.com

Hi @Kate_Sopko - So to get this to work, I need to create a new date field for the calendar view (which I think is what you are saying). DATETIME_PARSE is the right formula, but you specify the format of the date you are inputting to the formula (not the output format). DATETIME_PARSE always outputs dates in the same format:

M/D/YYYY h:mm a

So, on my example base, I created a new field “Calendar Date” and the formula for this was:

DATETIME_PARSE({Due Date and Time}, 'M/D/YYYY H:mm')

Where M/D/YYYY H:mm is the format of {Due Date and Time}

Does this fix the problem?

JB

That totally worked. I had a combination of problems-- date format, as you noticed, was off, and I also needed to be applying the formula to the formula field, not the one I’d formatted for Users to look at.

This is working great now. Many thanks!

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