Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Filter by Formula on Date not working

Topic Labels: API
Solved
Jump to Solution
2745 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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.