What is wrong with this Formula

[Abraham_Bochner]

Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.

IS_before(DATETIME_FORMAT(SET_TIMEZONE({Due Date}, “America/Los_Angeles”), “M/DD/YYYY”),DATETIME_FORMAT(SET_TIMEZONE(TODAY(), “America/Los_Angeles”), “M/DD/YYYY”))

What is the issue here? what is wrong here?

[kuovonne]

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())

[Abraham_Bochner]

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 !!!

[kuovonne]

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.

[Justin_Barrett]

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.

[Abraham_Bochner]

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

[kuovonne]

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()

[Abraham_Bochner]

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

[kuovonne]

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'
  )
)

[Abraham_Bochner]

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