Skip to main content

Hi folks, first-time poster here; I’ve looked around and tried some things but none of the solutions I’ve found worked, so here we go:


I currently have an IF formula that displays 3 different results based on the content of one column, and I am trying to display a 4th result based on the content of another column.


Here’s what I have so far:


IF({EXP}="",“UNLISTED”,

IF({EXP}<TODAY(),“EXP”,“ACTIVE”,

)

)


I’ve tried:


IF({EXP}="",“UNLISTED”,

IF({EXP}<TODAY(),“EXP”,“ACTIVE”,

IF({MARKET}=“AUCTION”,“AUCTION”

)

)

)


…but Airtable just deletes the third string. Other variations (IF/AND, IF/OR, and ELSE) generate an error.


I’m sure what I’m trying to do is simple enough, but it’s giving me a headache. Please help!


Thank you,


Shawn

Hey @Sales_Information!


Not sure which structure you wanted to stick with, but…


For this:



Try this:


IF(

    {EXP},

    IF(

        {EXP} < TODAY(),

        "EXP",

        "ACTIVE"

    ),

    "UNLISTED"

)

For…



You can try this:


IF(

    {EXP},

    IF(

        {EXP} < TODAY(),

        "EXP",

        IF(

            {MARKET} = "AUCTION",

            "AUCTION",

            "ACTIVE"

        )

    )

)

Let me know if something is still broken, or if you need any expanded help or explanation.


The reason that this doesn’t work is because the IF() function only accepts three arguments. Any arguments past that are ignored, so your test of the {Market} field isn’t even run.


Your message wasn’t very clear on how the “AUCTION” output is supposed to relate to the rest. Do you want to add that to the end of the string that’s generated by the first part? If so, you’ll need to concatenate that other IF() function to the output of the first. Try this:


IF(

  {EXP} = "", "UNLISTED",

  IF(

    {EXP} < TODAY(), "EXP", "ACTIVE"

  )

) & IF({MARKET} = "AUCTION", " AUCTION")

The reason that this doesn’t work is because the IF() function only accepts three arguments. Any arguments past that are ignored, so your test of the {Market} field isn’t even run.


Your message wasn’t very clear on how the “AUCTION” output is supposed to relate to the rest. Do you want to add that to the end of the string that’s generated by the first part? If so, you’ll need to concatenate that other IF() function to the output of the first. Try this:


IF(

  {EXP} = "", "UNLISTED",

  IF(

    {EXP} < TODAY(), "EXP", "ACTIVE"

  )

) & IF({MARKET} = "AUCTION", " AUCTION")

@Justin_Barrett Thanks so much. This created almost what I’m trying to do.


The only tweak would be that if the piece is going to AUCTION it doesn’t matter if it’s EXP anymore, so it’s not an additional output, it’s instead of.


In other words, if the EXP column is blank, it should read UNLISTED;

if the EXP column’s date is before today, it should read EXP; otherwise, it should read ACTIVE;

and if the MARKET column says “AUCTION”, it should read AUCTION -

regardless what the EXP column says.


If it’s not possible to do this, then the current solution is good enough, but I’d like to dial it in if possible.


Thanks again for your help!


Shawn


Here’s a screenshot to show what I’m working with:


@Justin_Barrett Thanks so much. This created almost what I’m trying to do.


The only tweak would be that if the piece is going to AUCTION it doesn’t matter if it’s EXP anymore, so it’s not an additional output, it’s instead of.


In other words, if the EXP column is blank, it should read UNLISTED;

if the EXP column’s date is before today, it should read EXP; otherwise, it should read ACTIVE;

and if the MARKET column says “AUCTION”, it should read AUCTION -

regardless what the EXP column says.


If it’s not possible to do this, then the current solution is good enough, but I’d like to dial it in if possible.


Thanks again for your help!


Shawn


Here’s a screenshot to show what I’m working with:




Thanks for the clarification. In that case, the “Auction” test should come first, and the rest after that.


I also optimized the test for an empty {EXP} field. Comparing a date field against an empty string will technically work, but NOT({Field Name}) is a streamlined way to look for almost any empty field. You can also omit the curly braces around the field names because they’re only single words with no special characters.


See if this does what you want:


IF(

    MARKET = "AUCTION", "AUCTION",

    IF(

        NOT(EXP), "UNLISTED",

        IF(

            EXP < TODAY(), "EXP", "ACTIVE"

        )

    )

)

Thanks for the clarification. In that case, the “Auction” test should come first, and the rest after that.


I also optimized the test for an empty {EXP} field. Comparing a date field against an empty string will technically work, but NOT({Field Name}) is a streamlined way to look for almost any empty field. You can also omit the curly braces around the field names because they’re only single words with no special characters.


See if this does what you want:


IF(

    MARKET = "AUCTION", "AUCTION",

    IF(

        NOT(EXP), "UNLISTED",

        IF(

            EXP < TODAY(), "EXP", "ACTIVE"

        )

    )

)

@Justin_Barrett YES!!! That did it! 🙌


Thank you so much!


Shawn

Reply


Terms & Conditions