Nested "IF" RYG

rodney.alexander2

I need a nested if for the following:

If percentage complete is less than 60% with three days left until due I would like it red.

If anything is past due it is red

If the percentage complete is less than 75% with two days left until due I would like it yellow.

All other green

Nik Fuentes

=if(And([percentage complete]@row < 0.6, [due date]@row-today()=3),"red", if([due date]@row-today()<0,"red", if(And([percentage complete]@row < 0.75, [due date]@row-today()=2), "yellow", "green")))

I'd check the brackets on this formula before using it, but this should work. The key here is to use an AND() function to get multiple conditions. You also get the 'time to due date' function by finding the difference between that date and today().

I am curious about your conditions though. why stop at 2 days out? shouldn't there also be one for 1 day out and the day of? Are 100% complete items that are past due also flagged as red? just curious and trying to potentially be helpful. If you have your reasons feel free to ignore.

rodney.alexander2

Nick, Those are really good points. I assumed (wrong) that 2 days would include 1 day as well. If they are 100% I would want them green.

I appreciate your comments and thoughts

rodney.alexander2

I plugged in the formula and it did not work... I am not sure what to do. It is making me crazy. At this point, I am not sure what to do..

rodney.alexander2

Nevermind... Got it