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]

Glad it works for you. Please report back tomorrow!

[Abraham_Bochner]

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

[kuovonne]

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.

[Abraham_Bochner]

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

[kuovonne]

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

[kuovonne]

Abraham_Bochner:

I need it badly to make it work 100%

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.