If statement formula not working

Options

Could someone please have a look at the formula and let me know, where my mistake is? I get an "unparseable" error.

Each argument by itself works!

Thanks for any input~

=IF([Start date 1]@row > TODAY(), "Gray"), IF(Status@row = "Completed", "Green"),IF(AND(Status@row = "In Progress", [Finish Date 1]@row >= TODAY(10)), "Green"), IF(AND(Status@row = "In Progress", [Finish Date 1]@row < TODAY(10), [Finish Date 1]@row >= TODAY(5)), "Yellow"), IF(AND(Status@row = "In Progress", [Finish Date 1]@row <= TODAY(5)), "Red"), IF(AND(Status@row = "Not Started", [Start date 1]@row <= TODAY()), "Red")

Best Answer

  • Teresa Drury
    Teresa Drury ✭✭✭
    Answer ✓
    Options

    Hi, Ute.

    You need to move the closing parenthesis that you have after each IF statement to the end of the complete statement.

    =IF([Start date 1]@row > TODAY(), "Gray", IF(Status@row = "Completed", "Green", IF(AND(Status@row = "In Progress", [Finish Date 1]@row >= TODAY(10)), "Green", IF(AND(Status@row = "In Progress", [Finish Date 1]@row < TODAY(10), [Finish Date 1]@row >= TODAY(5)), "Yellow", IF(AND(Status@row = "In Progress", [Finish Date 1]@row <= TODAY(5)), "Red", IF(AND(Status@row = "Not Started", [Start date 1]@row <= TODAY()), "Red"))))))

Answers

  • Teresa Drury
    Teresa Drury ✭✭✭
    Answer ✓
    Options

    Hi, Ute.

    You need to move the closing parenthesis that you have after each IF statement to the end of the complete statement.

    =IF([Start date 1]@row > TODAY(), "Gray", IF(Status@row = "Completed", "Green", IF(AND(Status@row = "In Progress", [Finish Date 1]@row >= TODAY(10)), "Green", IF(AND(Status@row = "In Progress", [Finish Date 1]@row < TODAY(10), [Finish Date 1]@row >= TODAY(5)), "Yellow", IF(AND(Status@row = "In Progress", [Finish Date 1]@row <= TODAY(5)), "Red", IF(AND(Status@row = "Not Started", [Start date 1]@row <= TODAY()), "Red"))))))

  • Ute Feger
    Options

    Thank you so much Teresa! This worked.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!