Nested IF Statement Help Needed


Hi there,

Here is what I am trying to achieve:

If the % Complete column =100%, then return Gray to the status

If a task is overdue (using the End Date column), return Red to the status

If a task is due within 5 days, return Yellow to the status

Is a task is due in 6 or more days, return Green to the status

If there is no date added to the End Date column, then leave the status blank (or a dash would be fine)

Here is the formula I am using: =IF([% Complete]@row = 1, "Gray", IF([End Date]@row < TODAY(), "Red", IF(AND([End Date]@row >= TODAY(), [End Date]@row < TODAY(+5)), "Yellow", IF([End Date]@row > TODAY(+5), "Green", "-"))))

It seems to be working except for that it is returning red to the status if there is no date entered. I also tried added an IF statement for ISBLANK but that didn't work either.

Thanks in advance for your help!


Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!