DATETIME_DIFF() and Incorrect Timezone

[Wayne_Olson]

Hey there, Airtable Experts…

I have created a simple task table where I am attempting to use a formula field to automatically categorize individual tasks based on their due dates, i.e., Overdue, Due Today, Due Soon (2 days or less), etc. I have used the following formula:

IF(DATETIME_DIFF({Due Date}, NOW(), 'days') < 0, "1. Overdue", IF(DATETIME_DIFF({Due Date}, NOW(), 'days') = 0, "2. Today", IF(DATETIME_DIFF({Due Date}, NOW(), 'days') <= 2, "3. Due Soon (2 days or less)", IF(DATETIME_DIFF({Due Date}, NOW(), 'days') <= 7, "4. Upcoming (7 days or less)", IF(DATETIME_DIFF({Due Date}, NOW(), 'days') > 7, "5. Future", BLANK())))))

The challenge I’m having is that the calculated difference between the due date and the current date yields the incorrect category, i.e., items due today are showing as overdue, and items showing due tomorrow are showing as due today. This leads me to believe the timezone is incorrect.

I have tried swapping NOW() for TODAY(), but that only yields the same result.

Anyone have any idea what the issue may be and how to correct it?

Many thanks for your help!

[Jeremy_Oglesby]

Try enabling the “Use same Timezone for all users” option only on the formula field (not on the date field).

[Wayne_Olson]

Thanks, @Jeremy_Oglesby, but I do not see that option in the Formula field anywhere, presumably because the output options for that field are not dates but text (e.g., “2. Today”).

[Jeremy_Oglesby]

Doh… :roll_eyes: I didn’t think that one through

[Elias_Gomez_Sai]

But there is a date in the Formula, that field has the option checked on?

[Wayne_Olson]

There’s no option, and I believe that option is only available if the output from the formula is a date.

[Jeremy_Oglesby]

This is kind of a round-about, hackey way to do this, but it may serve to verify what’s going on…

Try creating another formula field called “Today” with this formula:

DATETIME_FORMAT(
   TODAY(),
   'l'
)

And check to see that it returns the correct date.

Then try modifying it to:

DATETIME_FORMAT(
   SET_TIMEZONE(
      TODAY(),
      '<your timezone>'
   ),
   'l'
)

And see if it is still accurate. Try using that field as a go-between and reference it in your formula above and see if it changes anything.

[drassi]

Yeah timezones in airtable are fairly tricky and often screw up my filters as well, I wish there were better options for controlling them. In situations like this where local times and UTC times are getting confused in formulas & filters, I’ve used some of W_Van_Hall’s advice in using SET_TIMEZONE to slide times around to match your local timezone in which you are trying to have your filters apply. Something like:

SET_TIMEZONE and other time-related quirks Dates and timezones

Another way to restate a local time as UTC (for comparisons or whatever) is this DATEADD( NOW(), VALUE( DATETIME_FORMAT( SET_TIMEZONE( NOW(), 'America/Los_Angeles' ), 'ZZ' ) )/100, 'hours' ) The ‘ZZ’ format specifier causes DATETIME_FORMAT() to return the difference between local time and UTC as positive or negative hours and minutes, like so: '-0700'. To apply this shift, the formula converts that string to a numeric value and then div…

[Elias_Gomez_Sai]

Wayne_Olson:

that option is only available if the output from the formula is a date

I’m not talking about the Formula, but about {Due Date} field.

[Wayne_Olson]

Jeremy_Oglesby:

This is kind of a round-about, hackey way to do this, but it may serve to verify what’s going on…

Try creating another formula field called “Today” with this formula:

DATETIME_FORMAT(
   TODAY(),
   'l'
)

And check to see that it returns the correct date.

Then try modifying it to:

DATETIME_FORMAT(
   SET_TIMEZONE(
      TODAY(),
      '<your timezone>'
   ),
   'l'
)

And see if it is still accurate. Try using that field as a go-between and reference it in your formula above and see if it changes anything.

Brilliant! This has fixed the issue. Thanks, @Jeremy_Oglesby!

Now the question becomes: why in the world is Airtable setting my timezone as UTC?