Help

Re: Comparative date formula

Solved
Jump to Solution
1120 3
cancel
Showing results for 
Search instead for 
Did you mean: 
leandro_medeiro
6 - Interface Innovator
6 - Interface Innovator

Dear ones, I need help. I’m using a filter on another system that searches the result based on a certain date.
If I inform the filter as follows. Date = “2021-06-25”, The expectation is that I would return all the rows that appear on this date, however filtering this way I have no return.
If I use the Date filter >=“2021-06-25”. it works, but it brings me back not only the lines corresponding to that date but all the other dates ahead.

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

It’s a mystery why the >= operator works while the = operator doesn’t. Technically neither one should work because, as I said above, that formula comparing the Airtable date field—which stores the data in what’s called a datetime—against a string representation of a date.

If an exact match is what you really want, then what you could do is use a formula that formats the Airtable date similar to the formatting used in Landbot. Try this:

AND({company_}= '@company', DATETIME_FORMAT({data do agendamento}, 'MM/DD/YYYY') = '@date')

That will compare two similarly-formatted date strings, which should result in matches for some records.

See Solution in Thread

7 Replies 7
leandro_medeiro
6 - Interface Innovator
6 - Interface Innovator

What is the right way to filter an exact date in the airtable?

What’s this other system? Integromat? Zapier? Something else?

The reason that this doesn’t work is that you’re comparing the contents of a date field against a string. A string is any series of characters wrapped in quotes, and strings can’t be used to compare against dates. Once we know more about the other system that you’re using, we can offer specific guidance on how to correct this.

@Justin_Barrett Im using a landbot chatbot. in this bot, the client types a date to search, and then his answer goes to an airtable block that queries that date and brings back the lines that have that date filled out. however if I use the function “=“ it doesn’t work. and if I use “>=“ it lists the date that the customer entered and all the others that follow.

Could you please share a screenshot of your setup in the Landbot system, including where you have this condition entered?

The formula I’m using in the screenshot is:
AND({company_}= ‘@company’, {data do agendamento} >= ‘@date’).

image
image

Justin_Barrett
18 - Pluto
18 - Pluto

It’s a mystery why the >= operator works while the = operator doesn’t. Technically neither one should work because, as I said above, that formula comparing the Airtable date field—which stores the data in what’s called a datetime—against a string representation of a date.

If an exact match is what you really want, then what you could do is use a formula that formats the Airtable date similar to the formatting used in Landbot. Try this:

AND({company_}= '@company', DATETIME_FORMAT({data do agendamento}, 'MM/DD/YYYY') = '@date')

That will compare two similarly-formatted date strings, which should result in matches for some records.

thanks for the tip. I decided to do the reverse process. I created a new column in the airtable converting the date into string, so comparing string to string I can use the “=” symbol and now it’s working.