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:

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.

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:

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…

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

Well, strictly speaking, it’s not: Since collaborators can be anywhere in the world, datetime calculations default to UTC.

Something to keep in mind — and it still screws me up regularly — is that selecting the ‘Use the same timezone (GMT) for all collaborators’ box does not merely specify a formatting option: It causes a modification to the underlying data. Selecting/deselecting the box does not simply toggle the presented value back and forth.