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

Options
✭✭✭

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%

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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"))))

• ✭✭✭
edited 03/08/23
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭
Options

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!

• ✭✭✭
Options

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!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!