Skip to main content

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

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

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

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

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!

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!


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

 

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

Reply


Terms & Conditions