Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Nesting Formulas excluding future dates
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.
Comments
-
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
