How can I unflag tasks that have a status of 'Cancelled'?

I am looking to add a new condition to my At Risk column formula so that cancelled tasks are not flagged. My current formula flags all tasks that are past the completion date and are 100% complete. I want to keep it so that the ideal completion date and % complete is still visible so I still have that data, but I do not want "Cancelled" tasks to be shown at risk.


Current formula:

=IF(AND([% Complete]@row < 1, [Ideal Completion Date]@row < TODAY(), NOT(ISBLANK([Ideal Completion Date]@row))), 1, IF(COUNTIF(DESCENDANTS([At Risk]@row), =1) > 0, 1, 0))

Best Answer

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓

    @courtney.w You need to put the Cancelled portion of the formula first.

    =IF( [Ideal Completion Date]@row = "Cancelled", 0, IF(AND([% Complete]@row < 1, [Ideal Completion Date]@row < TODAY(), NOT(ISBLANK([Ideal Completion Date]@row))), 1, IF(COUNTIF(DESCENDANTS([At Risk]@row), =1) > 0, 1, 0)))

Answers

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓

    @courtney.w You need to put the Cancelled portion of the formula first.

    =IF( [Ideal Completion Date]@row = "Cancelled", 0, IF(AND([% Complete]@row < 1, [Ideal Completion Date]@row < TODAY(), NOT(ISBLANK([Ideal Completion Date]@row))), 1, IF(COUNTIF(DESCENDANTS([At Risk]@row), =1) > 0, 1, 0)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!