Filter Search By Date Range

[Scott_Heliker]

Trying to create a filter in integromat to search records for between todays date and 10 days in the future.
Can’t figure it out… Just playing around with some basic stuff I am getting weird returns

NOT({Start Date}<‘2017/11/15’)

That returned everything in 2018 and 2019 but did not factor in the month to include records greater than Nov 15 2017 like some that were Nov 25th 2017 and Dec 3 2017

Plan is to use the Date now merge tag in integromat and then date now+10 and bring back everything between today and 10 days from now so we can automate an events newsletter weekly only sending the cooresponding events that are happening in the next 10 days.

Appreciate any help…

[Justin_Barrett]

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)}}))

[Scott_Heliker]

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 ...

[Scott_Heliker]

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")}})

[Justin_Barrett]

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.

[Scott_Heliker]

Justin_Barrett:

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…

[Adrian_Ross]

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