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

For your reference, Airtable won’t let you save an improper formula. It lets you save formulas which return no value so long as they have sound syntax. I use formulas all the time which purposefully return a blank cell if conditions aren’t met.

As for why TODAY() isn’t working, I’m sure @W_Vann_Hall knows…

Hi James,

Both formulas work fine for me. Have you tried just TODAY() to see what it returns? Does it really return today’s date?

Regards,

Alex

It is 11:54PM EST, 6/4/19 and TODAY() returns 12:00AM 6/5/19 so no, it isn’t returning the correct date or time. Which might explain why =TODAY() isn’t working but the next question would be, why?

Sorry, I should rephrase my comment. You’re correct, it does not allow improper syntax. However, there are times when there is no clear indication what values are correct within a formula.

In my original example above should I write ‘days’, ‘day’ or leave it blank? All 3 give me the same result, a blank formula field. Is that causing a problem or not? I have no idea because the formula gives me no warnings.

My issue with Airtable formulas is that I don’t know what the problem is with the formula. I get no help from the app about why a formula is failing, just that it is. My other experience with similar formula syntax in Quickbase and Tableau has been that those apps will assist me in figuring out where the probelm is.

Hi James,

As I suspected, it’s due to the timezone difference between your Due Date and TODAY(). I have no idea what timezone used by TODAY() but there is a work-around to convert it to your current timezone as below

DATETIME_FORMAT(SET_TIMEZONE(TODAY(),‘America/Chicago’),‘MM/DD/YYYY’)

A list of supported timezones can be found at https://support.airtable.com/hc/en-us/articles/216141558-Supported-timezones-for-SET-TIMEZONE

After that you can use IS_SAME({Due Date}, {The new TODAY}, ‘days’) to compare as before.

Kind regards,

Alex

Thanks for your help Alex, I do appreciate it, but I think I will give up on this for now. It’s getting to be a waste of time, I’ve been working on this for nearly 3 hours now.

The DATETIME_FORMAT formula failed until I realized that the apostrophes weren’t in the correct font after I had pasted the formula in from this forum. No indication of why it was failing, good thing I checked the formatting.

I changed the time zone to ‘America/New_York’ because I am in New York. The formula, called {Time Fix}, returns 6/4/19 as today’s date which is not correct. It’s now 12:45AM on 6/5/19.

IF(IS_SAME({Due Date},{Time Fix},‘days’,“Due Today”)) does not give me any value return, no matter what date I enter for {Due Date}.

Keep in mind that formulas posted in the community might have “styled” quotes (as does the sample above) without forcing the code to use preformatted text. You’ll need to convert them to plain/non-styled quotes for the formula to operate correctly in Airtable:

DATETIME_FORMAT(SET_TIMEZONE(TODAY(),'America/Chicago'),'MM/DD/YYYY')

I’m having this same issue. It’s the strangest thing. When I use the following it sometimes shows yesterday’s date: DATETIME_FORMAT(SET_TIMEZONE(TODAY(), “America/Los_Angeles”), “M/DD/YYYY”) – Tried it last night and it showed me the correct date, revisited this morning and it’s showing yesterday’s date! Could this actually just be a bug? Incredibly frustrating :confused:

Hey @James_Trory1 - I know this is months later so not sure if you’re checking this thread, but I found a workaround after posting my response 4 hours ago. Wrapping your {Due Date} in the same DATETIME_FORMAT() and SET_TIMEZONE() should result in your formula working. This is what I came up with and it works:

IF(IS_SAME(DATETIME_FORMAT(SET_TIMEZONE({Due Date}, “America/Los_Angeles”), “M/DD/YYYY”),DATETIME_FORMAT(SET_TIMEZONE(TODAY(), “America/Los_Angeles”), “M/DD/YYYY”)), “1 - Due TODAY”, “”)

Its an annoying workaround, Airtable should display the correct date/time, but it works until that’s solved for.

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.