IF and IF/AND formula - Please help! thanks

Romi

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"

Please help me complete my formula.

Thank you!

Romi

Paul Newcome

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.

Romi

Thanks Paul!! I will try that.

Paul Newcome

Happy to help. 👍️

Romi

@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?

Thanks in advance.

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

Romi

@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?

Thanks in advance.

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

Paul Newcome

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?

Romi

@Paul Newcome

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

Paul Newcome

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?

Romi

@Paul Newcome

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

Paul Newcome

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

Romi

thanks Paul I will try this.

Romi

Paul Newcome

Happy to help. 👍️