Need assistance with unparseable project health formula using IF/AND statements

jackkelley

Current Formula:

=IF([% Complete]@row = 1, "Gray"), IF(AND(TODAY(5) < [Due Date]@row, [% Complete]@row < 1), "Red"), IF(TODAY(15) < [Due Date]@row, IF([% Complete]@row < 1, "Yellow", "Green"))))

Goal of formula:

1. Red = % Complete is less than 100% and < 5 days of Due Date
2. Yellow = % Complete is less than 100% and < 15 days of Due Date
3. Green = % complete is less than 100% and > 15 days of Due Date
4. Gray = % complete is 100%

Paul Newcome

Try this:

=IF([% Complete]@row = 1, “Gray”, IF([Due Date]@row⇐ TODAY(5), “Red”, IF([Due Date]@row⇐ TODAY(15), “Yellow”, “Green”)))

It looks like there has been some kind of formatting change here in the community. You will need to retype the formula in Smartsheet (don't copy/paste) to get the correct quotes and change the arrows to < followed by = for less than or equal to.

Paul Newcome

It looks like we just need to clean up some parenthesis. Give this a try:

=IF([% Complete]@row = 1, "Gray", IF(AND(TODAY(5) < [Due Date]@row, [% Complete]@row < 1), "Red", IF(TODAY(15) < [Due Date]@row, IF([% Complete]@row < 1, "Yellow", "Green"))))

jackkelley

https://community.smartsheet.com/discussion/comment/366327#Comment_366327

Have this exactly now, it is indicating “Gray” for status, but all other parameters are being ignored and indicating Red, regardless of Date/%

Paul Newcome

Try this:

=IF([% Complete]@row = 1, “Gray”, IF([Due Date]@row⇐ TODAY(5), “Red”, IF([Due Date]@row⇐ TODAY(15), “Yellow”, “Green”)))

It looks like there has been some kind of formatting change here in the community. You will need to retype the formula in Smartsheet (don't copy/paste) to get the correct quotes and change the arrows to < followed by = for less than or equal to.

jackkelley

This was it! I see now no need to reinsert %Complete reference as all IF statements after that were the false result for %Complete = 1. Thank you so much!

jackkelley

https://community.smartsheet.com/discussion/comment/366348#Comment_366348

This was it! I see now no need to reinsert %Complete reference as all IF statements after that were the false result for %Complete = 1. Thank you so much!