Need Help with IF, AND to create a Health Matrix that is based on DATE & % Complete

thunt06

I have figured out most of the equation to display the health of a task but I can't figure out how to get the Last criteria figured out.

Sample Case:

Task is 10 days in duration 1. Health is "Green" up to the 5th day. 2. If the task is below 50% complete and past 5 days then "Yellow". 3. If the task is above 50% complete and past 5 days then "Green". 4. If the task is past the 10 days and is not 100% complete then "Red". If the task reaches 100% then green. The task always starts out "Green" until the 5 day past the "Start Date".

This is what I have so far that is working but I don't know how to report on past the 10th day and NOT at 100% Complete. I can figure it out by itself but the rest of the equation is creating issues.

=IF(AND([Start Date]4 + 5 < TODAY(), [%]@row < 0.5), "Green", (IF(AND([Start Date]4 + 5 < TODAY(), [%]@row > 0.5), 2, (IF(AND([Start Date]4 + 5 > TODAY(), [%]@row < 0.5), "Yellow", (IF(AND([Start Date]4 + 5 > TODAY(), [%]@row > 0.5), 3, (IF(AND([End Date]< TODAY(), [%]@row <1),”Red”,9)))))))

Output: GREEN, 2 this is just to check the logic, Yellow, 3 this is just to check the logic, Red, 9 this is to see if any of the logic was used.

Thanks

Genevieve P.

Hi @thunt06

The first thing to note is that with a Nested IF statement, you can jump right into the next IF without adding an open parentheses in front of it. Not this, (IF, but this, IF. 

I would also suggest replacing your row number reference with @row, and combining your first two statements for Green to simply be that if 5 days past the Start Date is still in the future, it’s Green (no matter what the percentage is). I added in an OR to say that if it’s 100% (no matter what the dates are), then that’s also green.

Finally, your greater than and less than signs (> and <) were actually backwards. Greater than, >, indicates that the date is in the future. Less than, <, is looking for if the date is in the past. 

I have re-organized your statements, since Logic formulas read left-to-right and stop as soon as one criteria is met. 

Try this:

=IF(AND([End Date]@row < TODAY(), [%]@row < 1), "Red", IF(OR([Start Date]@row + 5 > TODAY(), [%]@row = 1), "Green", IF(AND([Start Date]@row + 5 <= TODAY(), [%]@row <= 0.5), "Yellow", IF(AND([Start Date]@row + 5 <= TODAY(), [%]@row >= 0.5), "Green"))))

Here are some Help Center articles that I used:

IF function / OR function / Formula Operators, such as Less Than / @row function

Let me know if this works, or if you have any questions!

Cheers,

Genevieve

Paul Newcome

We can actually shorten it even further with just a little bit of rearranging. If you put the "Green" section first, you can remove the AND from the "Red" portion since you are already establishing that [%]@row is less than 1 by default if it is able to move past the first portion, and using a nested IF logic on the "Yellow"/"Green" set with [Start Date]@row + 5 <= TODAY() means we can shorten that part a little bit as well by only having to type that once instead of twice within two different AND statements.

=IF(AND([End Date]@row < TODAY(), [%]@row < 1), "Red", IF(OR([Start Date]@row + 5 > TODAY(), [%]@row = 1), "Green", IF(AND([Start Date]@row + 5 <= TODAY(), [%]@row <= 0.5), "Yellow", IF(AND([Start Date]@row + 5 <= TODAY(), [%]@row >= 0.5), "Green"))))

=IF(AND([End Date]@row < TODAY(), [%]@row < 1), "Red", IF(OR([Start Date]@row + 5 > TODAY(), [%]@row = 1), "Green",

can be shortened to

=IF(OR([Start Date]@row + 5 > TODAY(), [%]@row = 1), "Green", IF([End Date]@row < TODAY(), "Red",

IF(AND([Start Date]@row + 5 <= TODAY(), [%]@row <= 0.5), "Yellow", IF(AND([Start Date]@row + 5 <= TODAY(), [%]@row >= 0.5), "Green"

can be shortened to

IF([Start Date]@row + 5 <= TODAY(), IF([%]@row <= .5, "Yellow", "Green"

Which takes your entire formula from

=IF(AND([End Date]@row < TODAY(), [%]@row < 1), "Red", IF(OR([Start Date]@row + 5 > TODAY(), [%]@row = 1), "Green", IF(AND([Start Date]@row + 5 <= TODAY(), [%]@row <= 0.5), "Yellow", IF(AND([Start Date]@row + 5 <= TODAY(), [%]@row >= 0.5), "Green"))))

to

=IF(OR([Start Date]@row + 5 > TODAY(), [%]@row = 1), "Green", IF([End Date]@row < TODAY(), "Red", IF([Start Date]@row + 5 <= TODAY(), IF([%]@row <= .5, "Yellow", "Green"))))

@Genevieve P That second portion is a good example of where we were talking about using a nested IF in place of replicating the same criteria within multiple AND statements.