Date Compare Issue

Jason_Friedlan1 · ‎Mar 25, 2021

I can not figure out why this is not working for me. I get the previous & future but anything for today comes up as Future.

IF({Meeting Date Calc}=Today(), “Today”,
IF({Meeting Date Calc}>Today(), “Future”,
IF({Meeting Date Calc}<Today(), “Previous”)))

Jeremy_Oglesby · ‎Mar 25, 2021

Hi @Jason_Friedlander

Dates are a strange animal on the web, and especially in Airtable. I can feel Airtable’s pain in the difficulty of handling date/time data gracefully on the web, but at the same time, Airtable does a terrible job of making it evident to the user what exactly is going on with date/time data.

Here’s a field with this simple formula in it:

TODAY()

I created that field at 12:02pm on 3/25/2021 local time for me. I screencaptured it at 12:03pm on 3/25/2021 local time for me.

That right there, probably illustrates the problem you are running into. The TODAY() function carries with it time data, but the time data seems to take “TODAY” to mean 12:00am of the current day, but on GMT. Then, when you try to use that “TODAY” data in a calculation, the browser is converting it to your local variant of that same time – ie, several hours behind GMT.

So in my case, it’s taking 12:00am 3/25 GMT, and converting it to 5:00pm 3/24 PDT :persevere: . Thanks, Airtable.

Since it seems you are only concerned with accuracy to the ‘day’, (and not to the hour, much less the minute) I would suggest giving these formula functions a try:

IF(IS_SAME({Meeting Date Calc}, TODAY(), 'day'), "Today",
...

And then secondarily, I’d suggest trying to play with the “Use the same time zone…” switch in the formula field formatting options – see whether on or off gets you more accurate results.

Justin_Barrett · ‎Mar 25, 2021

My understanding is that the “Use the same time zone…” switch only affects the display of the stored date, not the stored date itself. Under the hood, everything is stored relative to GMT, and then tweaked for display purposes (or not) based on the state of that switch.