Help with Health Formula

When I put this formula: =IF([% Complete]@row = "100%", "Green", IF(AND([% Complete]@row <> "100%", [Due Date]@row >= TODAY()), "Yellow", IF(AND([% Complete]@row <> "100%", [Due Date]@row < TODAY()), "Red", "")))

The status shows Red when the % complete is 100% and the Due date is past. How can I correct that?

Thanks

Best Answers

  • Mark.poole
    Mark.poole Community Champion
    Answer ✓

    @cvilla

    The issue comes into it finding everything false except the due date being less then today(). What exactly are you wanting it to show. Know that I can fix it for you. Keep in mind the way nested IF functions work is they will continue until it finds the first true statement.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 01/24/25 Answer ✓

    @cvilla

    Try this:

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

    Hope that helps!

    All the best,
    -Ray

Answers

  • Mark.poole
    Mark.poole Community Champion
    Answer ✓

    @cvilla

    The issue comes into it finding everything false except the due date being less then today(). What exactly are you wanting it to show. Know that I can fix it for you. Keep in mind the way nested IF functions work is they will continue until it finds the first true statement.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    edited 01/24/25 Answer ✓

    @cvilla

    Try this:

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

    Hope that helps!

    All the best,
    -Ray

  • cvilla
    cvilla ✭✭

    Thanks, the formula above helped.😀

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭

    @cvilla,

    Great news, and no problem, happy to help!

    All the best,
    -Ray

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!