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

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%

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!