IF AND

Ok. This works for me:

=IF([Estimated Days to Completion]@row > 10, "Green", IF([Estimated Days to Completion]@row <= 10, "Yellow"))

I can't get these to work:

Estimated Days to Completion is less than or equal to 0 and Status is not closed, should have red dot.

Estimated Days to Completion is less than or equal to 10 and greater than 0, should have yellow dot.

Status contains "On Hold" should have gray dot

• ✭✭✭✭✭✭

=IF(Status@row = "On Hold", "Gray", IF([Estimated Days to Completion]@row > 10, "Green", IF([Estimated Days to Completion]@row >= 0, "Yellow", IF(Status@row <> "Closed", "Red"))))

Give that a try.

• ✭✭✭✭✭✭
edited 02/06/20

You could simplify this by just making the yellow requirement be greater than 0. The first would fire at any days greater than 10. Try this... Red would be anything less than 0.

=IF(Status@row = "On Hold", "Gray", IF([Estimated Days to Completion]@row > 10, "Green", IF([Estimated Days to Completion]@row >= 0, "Yellow", "Red")))

• ✭✭✭✭✭✭

@Mike Wilday I thought about just going straight from Yellow to Red, but what if the Status is "Closed"? It would still eventually display a Red even though it is closed. I assumed that because it was specified about not being closed, that a status of closed shouldn't be red. Having said that... My solution would still show Green and Yellow for closed Statuses.

@Michael Figone Let us know what you think regarding the Status being closed vs not and the various color options.

• ✭✭✭✭✭✭

@Paul Newcome Good point. @Michael Figone You may want to have the status be Green if closed or blank if closed. Let us know either way.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!