Formula help for an IF(logical, value1, value2)

#1

So I’m using a formula in a column to pull data for another table.

This is my formula:
IF({Test Type} = “CAT 1”, {Date Only})
Which is basically: If the test type is CAT 1, then display the date from the ‘date only’ column.

My problem is this… sometimes tests are both CAT 1 AND CAT 5, but I still need it to pull the data. So what I really need is IF test type CONTAINS CAT 1, then display the date from the ‘date only’ column.

I used Fieldbook before Airtable and in that instance I was told to use a formula like this:
IF({Test Type}: “CAT 1”, {Date Only})
In this case, : was used to signify CONTAINS.

Is there something like that in Airtable?

Thanks!!

MATCH a partial string
Formula with IF and FIND
#2

You can use the FIND function here to do something like that, assuming you don’t have any other category types with a “1” in them.

The formula I’ve put in the formula column in this screenshot is:
IF(FIND(“1”, {Test Type}) >= 1, DATETIME_FORMAT({Date Only}, ‘M/D/YYYY’)," ")

Let me know if you have any questions!

Zapier Advanced Search if field “CONTAINS”
#3

Thank you @Katherine_Duh, that worked perfectly!

I do have another question, however. I am trying to use this returned date in a formula on another table. I have a field for the lookup, but I’m having trouble with the MAX function.

I want to show the last date, if the date is before today. It’s just returning a 0 or ERROR when using MAX function

This is the closest I’ve gotten to a formula that MIGHT work: IF(IS_BEFORE({Date if CAT 1}, TODAY()), DATETIME_FORMAT({Date if CAT 1}, ‘M/D/YYYY’))
The only problem with that is that as soon as it has more than one date to pick from, it gives me an error.

I tried using a rollup field to show me the MAX date from the linked table, but that only returns a 0. How can I get it to return a date?

What’s the easiest way to go about what I’m trying to accomplish?

Thanks!!

EDIT: I would also like to have this output be M/D/YYYY. right now this formula returns a date with a time, even though the date it’s pulling from has no time. DATEADD({Last CAT 1}, 6, ‘months’)

DOUBLE EDIT: I can’t get this formula to work, either!
IF ({Date if CAT 1} >= TODAY(), DATETIME_FORMAT({Date Only}, ‘M/D/YYYY’))

I’m sort of trying to combine that above formula and this one to be what I’m looking for.
IF (FIND(“1”, {Test Type}) >= 1, DATETIME_FORMAT({Date Only}, ‘M/D/YYYY’))
(If the Date is before today and has the test type of 1, show Date Only in M/D/YYYY format)

#4

@Sarah_Cliff you need to format the date to get rid of the time:

https://support.airtable.com/hc/en-us/articles/215646218-Formulas-and-date-fields

They should really fix that :neutral_face:

#5

The field I’m referencing in the MAX() function rollup field is already formatted to just have the date with no time. The original field has date and time, then I have a function to show just date only, and that’s the field i’m trying to rollup with the MAX() function.

It’s just still showing me zero. :confused:

#6

I have similar problems. It seems that a rollup of values coming out of a formula field don’t work and give only 0 values.