Options
✭✭

Hello,

My current formula that I have is =IF(Status@row = "Complete", "Green", IF(Status@row = "On Target", "Green", IF(Status@row = "In Jeopardy", "Yellow", IF(Status@row = "On-Hold", "Yellow", IF(Status@row = "Past Due", "Red", IF(Status@row = "Archived", "Green", IF(Status@row = "Complete Past Due", "Green", IF(AND(Status@row <> "Complete", [End Date]@row <= TODAY(+7), "Yellow", IF(AND(Status@row = "In Jeopardy", [End Date]@row <= TODAY(-7), "Red", IF(AND(Status@row = "On Target", [End Date]@row <= TODAY(+7), "Red")))))))))))))

For some reason all the IF statements seem to work by the result I am getting but the IF/AND statements are being ignored.

I need to add the 3 following IF/AND conditions after all the IF statements on the above example:

1. If Status is not "Complete" and within 7 days prior to the End Date, then it should be "Yellow
2. If Status is "On Target" and within 7 days after the End Date then "Red"
3. Lastly, If Status is "In Jeopardy" and within 7 days after the End Date then "Red"

Thank you!

Romi

• ✭✭✭✭✭✭
Options

The problem is that nested IF statements read from left to right and stop on the first true value. So (for example) if the Status is On Target and the End Date is within the next 7 days, the Status being On Target will hit that second IF statement, see that it meets that criteria, then stop running. Try reordering the IF statements so that your ANDs come first.

• ✭✭
Options

Thanks Paul!! I will try that.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭
Options

Good mornning Paul.

I am still struggling on this one, could you provide me an example on how I need to line up the formula from left to right so that no single condition will be ignored in the below example please?

e.g. If the status is COMPLETE, then green, if the Status is not complete and within 7 days prior to the END DATE.

Romi

• ✭✭
Options

@Paul Newcome

Good mornning Paul.

I am still struggling on this one, could you provide me an example on how I need to line up the formula from left to right so that no single condition will be ignored in the below example please?

e.g. If the status is COMPLETE, then green, if the Status is not complete and within 7 days prior to the END DATE then yellow.

Romi

• ✭✭✭✭✭✭
Options

You actually have quite a bit that overlaps or contradicts other portions of the formula. Are you able to spell out of of your different criteria you need built into the formula?

• ✭✭
Options

Paul,

These are the different criteria i need to build.

If the STATUS is "COMPLETE" then Schedule Health row should be "GREEN"

IF the STATUS is not equal "COMPLETE" and is within 7 days prior to END DATE – flip Schedule Health to YELLOW.

If the STATUS is "On Target" then Schedule Health row should be "GREEN"

If the STATUS is "On Target" and today's date is within 7 days after the END DATE, then schedule health should be RED

If the STATUS is "In Jeopardy" then Schedule Health row should be "YELLOW"

If the STATUS is "In Jeopardy" and today's date is within 7 days after the END DATE, then schedule health should be "RED"

If the STATUS is "On Hold", then Schedule Health should be "YELLOW"

If the STATUS is "Complete Past Due", then Schedule Health should be "GREEN"

If the STATUS is "ARCHIVED" then Schedule Health should be "GREEN"

thanks!

Romi

• ✭✭✭✭✭✭
Options

Can you please furhter explain the two bold portions below?

If the STATUS is "COMPLETE" then Schedule Health row should be "GREEN"

IF the STATUS is not equal "COMPLETE" and is within 7 days prior to END DATE – flip Schedule Health to YELLOW.

If the STATUS is "On Target" then Schedule Health row should be "GREEN"

If the STATUS is "On Target" and today's date is within 7 days after the END DATE, then schedule health should be RED

If the STATUS is "In Jeopardy" then Schedule Health row should be "YELLOW"

If the STATUS is "In Jeopardy" and today's date is within 7 days after the END DATE, then schedule health should be "RED"

If the STATUS is "On Hold", then Schedule Health should be "YELLOW"

If the STATUS is "Complete Past Due", then Schedule Health should be "GREEN"

If the STATUS is "ARCHIVED" then Schedule Health should be "GREEN"

The way I read them, if the end date is in the past 7 days then "Red". But what if the end date is 8 days in the past?

• ✭✭
Options

Paul,

Im sorry for the confusion... I have updated and corrected the note below, thanks!

If the STATUS is "On Target" and END DATE has been past due by 7 days or more, then schedule health should be "RED"

If the STATUS is "In Jeopardy" and END DATE has been past due by 7 days or more, then schedule health should be "RED"

thanks,

Romi

• ✭✭✭✭✭✭
Options

Ok. Try this...

=IF(OR(CONTAINS("Complete", Status@row), "Status@row = "ARCHIVED"), "Green", IF(OR(Status@row = "On Hold", [End Date]@row <= TODAY(7)), "Yellow", IF([End Date]@row + 7 <= TODAY(), "Red", IF(Status@row = "In Jeapordy", "Yellow", "Green"))))

• ✭✭
Options

thanks Paul I will try this.

Romi

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!