Nesting Formulas excluding future dates

Edgar Montalvo

Hallo Everyone

 

I have a Nested Formula that looks like this.

 

=IF((Eficiencia19) >= 0.9, "Green", IF((Eficiencia19) <= 0.7, "Red", IF((Eficiencia19) > 0.7, "Yellow")))

 

What it does , it automatically put a symbol, depending of the value of a cell. It does this independendly of the date.

Green :  >=0.90

Yellow: > 0.70 and < 0.90 ( a little tricky)

Red:  <= 0.70

 

How can i add a condition that put the red symbol only on task that meet that criteria (done), but exclude the task that are in the future.

 

Thanks.

 

 

 

 

J. Craig Williams

Edgar,

 

Try this for a date colum [Due Date]

 

=IF((Eficiencia19) >= 0.9, "Green", IF((Eficiencia19) > 0.7, "Yellow", IF(AND([Due Date]19 <= TODAY(), (Eficiencia19) <= 0.7), "Red")))

 

I don't know what you mean by "a little tricky", but I suspect it is how you had the order.

IF statements stop calculating once the get to a solution

 

IF (conditions for green) is true, then the other conditions don't matter.

So, if Eficiencia >= 0.9 green, > 0.7 yellow,  <= 0.7 AND in the future red.

Note that this:

 

=IF((Eficiencia19) >= 0.9, "Green", IF((Eficiencia19) > 0.7, "Yellow", IF([Due Date]19 <= TODAY(), "Red")))

 

also works (and is the one I recommend)

 

Why, because if you get to the third IF check, Eficiencia MUST be <= 0.7, so we don't need to bother checking for it.

If, however, you might change the criteria, then it might be better to use the first one to make it clear that is the case.

 

Hope this helps.

 

Craig