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

jackkelley
jackkelley ✭✭✭
in Formulas and Functions

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%

· Share on FacebookShare on Twitter

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.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!