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]

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

[Kate_Sopko]

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

[Jeremy_Oglesby]

Kate_Sopko:

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

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

[JonathanBowen]

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

[JonathanBowen]

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

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

[Kate_Sopko]

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

Virus-free. www.avast.com

[Kate_Sopko]

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

[Kate_Sopko]

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

[JonathanBowen]

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

[Kate_Sopko]

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!