Skip to main content

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

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!

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!


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)

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)


@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:

@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:


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:

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:


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.

Terms & ConditionsAccessibility statement