What is wrong with this formula?

I'm trying to change the status based on % complete column:


=IF([Percent Complete]@row = 1, "Complete", IF([Percent Complete]@row = 0, "Not Started", IF(AND([Percent Complete]@row < 1, [Finish Date]@row < TODAY()), "Delayed", IF(AND([Percent Complete]@row < 0.5, [Finish Date]@row <= TODAY(5)), "At Risk", IF(AND([Percent Complete]@row < 1, [Finish Date]@row > TODAY(7)), "In Progress", IF(AND([Percent Complete]@row > 0.5, [Finish Date]@row < TODAY(7)), "In Progress", “""))))))


Thank you!!

Best Answer

  • Christian Graf
    Christian Graf ✭✭✭✭✭
    Answer ✓

    =IF([Percent Complete]@row = 1, "Complete", IF([Percent Complete]@row = 0, "Not Started", IF(AND([Percent Complete]@row < 1, [Finish Date]@row < TODAY()), "Delayed", IF(AND([Percent Complete]@row < 0.5, [Finish Date]@row <= TODAY(5)), "At Risk", IF(AND([Percent Complete]@row < 1, [Finish Date]@row > TODAY(7)), "In Progress", IF(AND([Percent Complete]@row > 0.5, [Finish Date]@row < TODAY(7)), "In Progress", ""))))))

    Too many quotation marks at the end.

Answers

  • Christian Graf
    Christian Graf ✭✭✭✭✭
    Answer ✓

    =IF([Percent Complete]@row = 1, "Complete", IF([Percent Complete]@row = 0, "Not Started", IF(AND([Percent Complete]@row < 1, [Finish Date]@row < TODAY()), "Delayed", IF(AND([Percent Complete]@row < 0.5, [Finish Date]@row <= TODAY(5)), "At Risk", IF(AND([Percent Complete]@row < 1, [Finish Date]@row > TODAY(7)), "In Progress", IF(AND([Percent Complete]@row > 0.5, [Finish Date]@row < TODAY(7)), "In Progress", ""))))))

    Too many quotation marks at the end.

  • Thank you so much! I have another one... how do I add in "Delayed" when there is a Delayed in the children. I'm currently using the formula below and when I try and add in delayed I get unparseable. I generally have a difficult time trying to add to any formula... Thanks!!


    =IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") = COUNT(CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "At Risk") > 0, "At Risk", "---"))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!