Filter by Formula on Date not working

[Ruchika_Abbi]

Hi,

I’m using Filter By Formula to find records based on Last Modified Date;
As per Codepen Airtable URL encoder I get the following syntax;
(IS_AFTER(%7BLast+Modified+Date%7D%2C+%222%2F23%2F2020%22)+%3E%3D+0)

While syntactically this works fine, the results I get are not correct. I get dates before the given date too.

Any suggestions?

Regards,
Ruchika

[kuovonne · Accepted solution]

Here are some possible causes:

A timezone issue

If your dates are off by only a day or a few hours, it could be a time zone issue.

You may need to use the DATETIME_PARSE() function on your date and set the locale.

DATETIME_PARSE("2/23/2020", "MM/DD/YYYY", 'locale')

Here is a modified version of your snippet before URL encoding. Be sure to change 'locale' to the actual locale you want.

IS_AFTER({Last Modified Date}, DATETIME_PARSE( "2/23/2020", "MM/DD/YYYY", 'locale'))

You also should not need to compare the result of IS_AFTER to 0. The function returns 0 (false) or 1 (true).

Calculated fields

The {Last modified time} field type is based on only changes made by a user in a non-computed field. If a value changes in a computed field (such as in a lookup or rollup), the {Last modified time} will not change, even though the record will appear to change in the view.

Different field

The {Last modified time} field might be based off a different field than you expect.

[Ruchika_Abbi]

Thanks!

I realised on reading your response that I’m comparing IS_AFTER result as >=0 :woman_facepalming:

Changed it now.