# IF AND for Status Change

Topic Labels: Formulas
Solved
1386 5
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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

Accepted Solutions
11 - Venus

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"
)
)
)``````
11 - Venus

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"
)
)
)
)``````

5 Replies 5
11 - Venus

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"
)
)
)``````
5 - Automation Enthusiast

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

5 - Automation Enthusiast

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!

11 - Venus

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"
)
)
)
)``````

5 - Automation Enthusiast

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