What is wrong with this Formula

You do not need to use DATETIME_FORMAT(), which converts the date information into a text string. The IS_BEFORE function wants the original date data, not the pretty formatted string version of the date.

IS_BEFORE({Due Date},TODAY())

IS_BEFORE({Due Date},TODAY())

I need it, because by the end of the near 12:00 AM, the “today” is looking already for the tomorrow date

something is happening then !!! which I can not figure out !!!

Ah, timezone problems can be tricky to suss out.

Does TODAY() return tomorrow’s date before or after 12:00am? Having TODAY() return the next morning’s date at 12:00am is standard behavior for computers. However, if TODAY() is returning tomorrow’s date when it is still 11:59PM, that is a timezone issue.

Does your {Due Date} field include a time? If it included a time at some point, even if it doesn’t now, there might be some hidden time in the fields that is leading to the time zone problems.

You don’t actually need DATETIME_FORMAT. As stated above, that only formats a text output version of the date. It doesn’t actually change the internal date used for comparison.

Try switching TODAY() to NOW(). One of the devs pointed out to me that TODAY() is based in GMT, but NOW() is supposed to use your local time.

When I changed to “now” I am getting a 1 even for today’s date!!!

When it’s backed to “Today”, I am getting a 0, which is good till about a few hours before 12:00 AM

Most date fields without a time field have a hidden time of 12:00am. That is why today’s dates are showing up as before NOW().

You could use TODAY() combined with DATEADD() to add the proper number of hours to deal with the GMT offset.

Or you could use NOW() and and grab the MONTH(NOW()), DAY(NOW()), and YEAR(NOW()), and parse them using DATETIME_PARSE()

Can you please help me write out the formula of your second option?

The formula field reference has all the information you need to build this formula, but it can take some digging. I also recommend writing the formula using multiple lines outside of Airtable and then copy/paste it.

DATETIME_PARSE( 
  DAY(NOW()) & "/" & MONTH(NOW()) & "/" & YEAR(NOW()), 
  'DD/MM/YYYY'
)

In the main formula …

IS_BEFORE(
  {Due Date},
  DATETIME_PARSE( 
    DAY(NOW()) & "/" & MONTH(NOW()) & "/" & YEAR(NOW()), 
    'DD/MM/YYYY'
  )
)

DATETIME_PARSE( 
  DAY(NOW()) & "/" & MONTH(NOW()) & "/" & YEAR(NOW()), 
  'DD/MM/YYYY'
)

IS_BEFORE(
  {Due Date},
  DATETIME_PARSE( 
    DAY(NOW()) & "/" & MONTH(NOW()) & "/" & YEAR(NOW()), 
    'DD/MM/YYYY'
  )
)

OK I did it, Thanks you very much

I need to wait for another 6-8 hours, a few hours before 12:00 AM, to see how it looks like

Glad it works for you. Please report back tomorrow!

Not good , its 11:00 PM here in NY & I am getting this one

Using this Formula

IS_BEFORE({Due Date},DATETIME_PARSE(
DAY(NOW()) & “/” & MONTH(NOW()) & “/” & YEAR(NOW()),
‘DD/MM/YYYY’
))

It really sounds like a timezone and/or a daylight savings time issue.

I looked around a bit, and I think that NOW() might still be based on GMT, not the local time. When I use it in a formula by itself, there is an option to display using the local timezone, but internally it is still stored as GMT.

So what should I do?

How can I fix this? I need it badly to make it work 100%

Again, Thanks all for your help

As I said initially, time zone problems can be difficult to suss out.

There are many factors in play:

• the time zone used to store the underlying data
• the format settings for the date/time field
• the format settings for formulas that produce a date/time
• the actual time zone of the user
• the time zone of the computer the user is using

These are simply to many moving parts to diagnose over the internet.
My recommendation is to convert your date fields so that they show the time as well as the date. Make sure that all fields are formatted to use the same time zone (GMT). Then make the comparisons based on viewing the time information.

Date/time formatting

When diagnosing date/time and timezone issues, have all involved fields display a time, and also use the GMT time zone.

Date/time settings for a date/time field:

Date/time formatting for a formula field that returns a date/time:

TODAY() vs NOW()

TODAY() returns the current date with a time of 12:00am (GMT).
NOW() returns the current date and time (GMT).
Both are stored as GMT, but can be displayed as a local time.

Viewing the actual underlying date/time data

To see the underlying time that is actually stored without Airtable formatting it for your timezone, concatenate the time field with a text string. The resulting string will be ugly, but will reveal the actual time in the field.

"The time is " & NOW()

Would you mind sharing more about your use case? It is unusual to need to have the date flip exactly at 12:00am local time. Also keep in mind that the TODAY() and NOW() fields are not continuously updated. They are only updated when viewed. Thus, if no-one is accessing any records in the middle of the night, they will not be updated.