filterByFormula to pull specific (but changing) date range

[Heavy_Sound]

Hey,

I have a table with records that are tagged with a specific day of the month. I want to pull the 5 records associated with the previous 5 days (not including today) with a filterByFormula parameter. I am using the Node.js Airtable library.

How would you do this?

[Erin_OHern]

Hi there,

Thanks for your question!

To use the filterByFormula parameter, you’ll want to write an Airtable formula that will return true for the records that you want to select. We have to check two things in our formula:

1. that the date is before today
2. that the date is 5 days ago or later (i.e., the date is after the day that was 6 days ago)

We can write the following Airtable formula to check both of those conditions:

AND(
    IS_BEFORE({date}, TODAY()),
    IS_AFTER(
        {date},
        DATETIME_DIFF(
            TODAY(),
            -5,
            'days'
        )
    )
)

To get this formula to work, you’ll first need to convert your date field to the Date field type, since it appears to be a text field currently. Once you do that, you can use the formula functions that treat it as a date.

For more on Airtable formulas, see this reference guide.

I hope this helps! Feel free to follow up if you’re still running into issues.