Can't get TODAY() to know when today is

[James_Trory1]

I have a very simple formula request that is blowing my mind right now. I don’t know why this is so difficult.

If the field {Due Date} is today, return the value “Due Today” in the formula field. First I tried:

IF({Due Date}=TODAY(),“Due Today”)

Pretty standard. But amazingly that won’t return “Due Today” if the date is today, only if the date is in the future :man_facepalming:t3:

I read here in the community forum that this would work instead:

IF(IS_SAME({Due Date},TODAY(),‘days’),“Due Today”)

Which doesn’t work either. The formula field remains blank in this case, no matter what the date is. I’ve tried ‘days’, ‘day’ and removing it completely - the formula is accepted but returns no value.

Which btw, I find really frustrating. Don’t let me save a formula that doesn’t work.

I’m so confused I don’t even know where to start trying to troubleshoot it. This is the kind of thing that puts me off using Airtable. It’s such a simple formula which in any other database software is routine. Can someone help me crack this enigma code please?

[Bill_French]

Nicolette_Garcia:

Its an annoying workaround, Airtable should display the correct date/time

I don’t think this is an error by Airtable; its behavior is likely entirely intentional.

In database management system it is common practice – indeed, regarded as a BEST practice – to store and manage all dates in GMT (Zulu) format and it appears the Today() function does exactly that.

As far as Airtable is concerned, you’ve asked it for the current date without qualifying the answer to your specific location.

If you believe Airtable should recast Today() and Now() into values based on your timezone, I can give you dozens of examples why this will fail for you and other users in different timezones.

Background

Databases are designed to store dates efficiently minimizing space while making queries as fast as possible. Because integers are easier to query, index, and more space-efficient than strings, dates are usually stored as 64-bit integers such as a UNIX epoch with milliseconds.

Databases (by-and-large) will convert any DateTime into a UTC epoch to store internally. However, some databases may enable storing timezone information. If that’s the case, it’s recommended to convert all dates to UTC before storing them.

Don’t use the local timezone or the users’ timezone. Otherwise, you’ll be pulling your hair out when your database is deployed in high availability servers across multiple data centers and across multiple timezones.

Just know that when you ask a database platform what Today() is, lacking any timezone offset, it will give you the exact answer in Greenwich Mean Time (GMT). The same applies to Now() and many other date/time values in Airtable and for good reason.