IF/AND Formula not working

[Iris_Maubon]

Hi Airtable community !

I’m new to formulas, loving it but struggling a bit as well…

I’m trying to automate a change in my records, from a phase of the process to the next, at a specific date using IF/AND.

I created a fromnow field called “Calculation” and when it gets to 0 days, I would like to change my Phase en cours to the next phase. It looks like this :

IF(
AND(
{Calculation} = “0 days”,
{Phase en cours}= “Envol 2”
),
“Accélération 3”,

IF(
AND(
{Calculation} = “0 days”,
{Phase en cours}= “Accélération 3”
),
"Accélération 4”,

IF(
AND(
{Calculation} = “0 days”,
{Phase en cours}= “Accélération 4”
),
“Propulsion 5”,

IF(
AND(
{Calculation} = “0 days”,
{Phase en cours}= “Propulsion 5”
),
“Propulsion 6”,

IF(
AND(
{Calculation} = “0 days”,
{Phase en cours}= “Propulsion 6”
),
“Coaching terminé”,

)
)
)
)
)

It’s unfortunately not working and I can’t figure out why :frowning: (it says “Sorry, there was a problem creating this field. Invalid formula. Please check your formula text.”)

Thanks for your help !

[Justin_Barrett · Accepted solution]

Most of it looks fine, but toward the end of your formula, you have this:

"Coaching terminé",

There should not be a comma after that string unless you’re providing the third argument for the IF() function. Remove that comma, and it should work.

That said, there’s a lot that could be optimized with this. First off, because {Calculation} = "0 days" is a part of every condition, do that once in an outer-level IF() function, then only check the {Phase en cours} field in the inner tests. No need to use AND() every time.

On top of that, because you’re just switching the output based on what’s in that {Phase en cours} field, you could further optimize that central portion with the SWITCH() function.

With those changes, your formula could be this:

IF(
    {Calculation} = "0 days",
    SWITCH(
        {Phase en cours},
        "Envol 2", "Accélération 3",
        "Accélération 3", "Accélération 4",
        "Accélération 4", "Propulsion 5",
        "Propulsion 5", "Propulsion 6",
        "Propulsion 6", "Coaching terminé"
    )
)

[Iris_Maubon]

Oh wow that worked like a charm, thanks so much !!