Filter Search By Date Range

Your test formula is comparing a datetime value against a string, which is likely why you’re getting unpredictable results (frankly I’m surprised it’s even partially working). It’s also more reliable to use Airtable’s IS_BEFORE and IS_AFTER date-comparison functions. Try this instead:

NOT(IS_BEFORE({Start Date}, DATETIME_PARSE("2017/11/15", "YYYY/MM/DD")))

For your actual use case where you’re checking for items within a range of dates, you’d likely want something like this (which you should be able to paste directly into Integromat’s formula field in the Airtable search module, because that’s where I copied it from):

AND(IS_AFTER({Start Date}, {{now}}), IS_BEFORE({Start Date}, {{addDays(now; 10)}}))

Hey Justin thanks… I got an error because the now was adding the time and seconds to the end so I formatted the date for YYYY-MM-DD… Got rid of that error but have another one for the add days area…

AND(IS_AFTER({Start Date},{{formatDate(now; “YYYY-MM-DD”)}}),IS_BEFORE({Start Date},{{addDays(formatDate(now; “YYYY-MM-DD”; 14) + “))”)}}

[Album] Imgur

Had the bottom date nesting formula working by itself but not thrown in the mix… Not sure what the hiccup is… Seems to be stripping the + sign also in front of 14

[Album] Imgur

indent ---   AND(IS_AFTER({Start Date},{{formatDate(now; "YYYY-MM-DD")}}),IS_BEFORE({Start Date},{{formatDate(addDays(now; 14); "YYYY-MM-DD")}})

Scott_Heliker wrote:

Had the bottom date nesting formula working by itself but not thrown in the mix… Not sure what the hiccup is… Seems to be stripping the + sign also in front of 14

[Album] Imgur

indent ---   AND(IS_AFTER({Start Date},{{formatDate(now; "YYYY-MM-DD")}}),IS_BEFORE({Start Date},{{formatDate(addDays(now; 14); "YYYY-MM-DD")}})

You don’t want to use Integromat’s formatDate function. That turns the date back into text. It needs to stay as a date for the IS_BEFORE and IS_AFTER functions to operate properly.

I don’t think that time and seconds have any bearing on it. It’s actually my own fault for not actually testing that earlier formula before I passed it along. I somehow overlooked the fact that even though Integromat would translate its now variable into a datetime object for its own use, it needs to be passed into Airtable for formula execution, so it becomes a string, which has to be parsed before Airtable can compare the dates. Here’s a formula that has been tested:

AND(
    IS_AFTER(
        {Start Date},
        DATETIME_PARSE("{{now}}")
    ),
    IS_BEFORE(
        {Start Date},
        DATETIME_PARSE("{{addDays(now; 14)}}")
    )
)

Just as multi-line functions work in Airtable, this will work as-is in Integromat.

AND(
    IS_AFTER(
        {Start Date},
        DATETIME_PARSE("{{now}}")
    ),
    IS_BEFORE(
        {Start Date},
        DATETIME_PARSE("{{addDays(now; 14)}}")
    )
)

Beautiful… Thank you for your help Justin. Works great!!
Easier to decipher when broken up like that also…

AND(
    IS_AFTER(
        {Start Date},
        DATETIME_PARSE("{{now}}")
    ),
    IS_BEFORE(
        {Start Date},
        DATETIME_PARSE("{{addDays(now; 14)}}")
    )
)

Just chiming in to say thanks for this solution, Justin! I really appreciate it!