Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Filter by Formula on Date not working

Topic Labels: API
Solved
Jump to Solution
3898 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Ruchika_Abbi
5 - Automation Enthusiast
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

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.

See Solution in Thread

2 Replies 2
kuovonne
18 - Pluto
18 - Pluto

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.

Thanks!

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

Changed it now.