IF AND for Status Change

[lucas__mar]

Hello, I'm trying to create a Formula that will change Status according to the fields "Start Date" & "End Date". 

If Today > End Date = "Delayed"

If Today < End Date = "In Progress"

If Today < Start Date = "Planning"

I have written the following formula that works for "Delayed", but shows "True" instead of "In Progress" or "Planning"

IF(TODAY() > {End Date},"Delayed", AND(IF(TODAY() < {End Date},"In Progress"),AND(IF(TODAY() < {Start Date},"Planning"))))

Anyway can identify my mistake? Thank you!!!

[Ben_Young1 · Accepted solution]

Hey @lucas__mar! 

Give this a shot:

IF(
    {End Date},
    IF(
        TODAY() > {End Date},
        "Delayed",
        IF(
            TODAY() < {End Date},
            "In Progress",
            IF(
                TODAY() < {Start Date},
                "Planning"
            )
        )
    ),
    IF(
        {Start Date},
        IF(
            TODAY() < {Start Date},
            "Planning"
        )
    )
)

[lucas__mar]

This worked like a charm @Ben_Young1 
Thank you VERY VERY much 😃

[lucas__mar]

Hey @Ben_Young1 I'm trying to add a "Done" to that field, based on a value in an added single select field named "Completion" (Yes, No). The idea is that when the field is changed to "Yes", then the Status field changes to "Done". I tried with the IFs of your formula, but can't seem to get it right. Thxs for the help!

[Ben_Young1 · Accepted solution]

Hey @lucas__mar! 

Glad to see that the original formula worked as you'd hoped.
For the edit you're describing, give this a shot and let me know if it works or if something goes funky with it.
I haven't been able to test it yet, so I might have missed something:

IF(
    {Completion},
    SWITCH(
        {Completion},
        "Yes",
            "Done",
        "No",
            IF(
                {End Date},
                IF(
                    TODAY() > {End Date},
                    "Delayed",
                    IF(
                        TODAY() < {End Date},
                        "In Progress",
                        IF(
                            TODAY() < {Start Date},
                            "Planning",
                            ""
                        )
                    )
                ),
                IF(
                    {Start Date},
                    IF(
                        TODAY() < {Start Date},
                        "Planning",
                        ""
                    )
                )
            )
    ),
    IF(
        {End Date},
        IF(
            TODAY() > {End Date},
            "Delayed",
            IF(
                TODAY() < {End Date},
                "In Progress",
                IF(
                    TODAY() < {Start Date},
                    "Planning"
                )
            )
        ),
        IF(
            {Start Date},
            IF(
                TODAY() < {Start Date},
                "Planning"
            )
        )
    )
)

[lucas__mar]

Thank you again @Ben_Young1 this is perfect! I think after spending a few minutes I now also understand the logic behind. It's a good time to import that Formulas Base that's around and study them in depth hehe

Thank you!!!!