How do I Automate the At Risk Column?

I need the at risk column to be automatically checked if a task is overdue (end date is in the past) and the (status) column is not complete or N/A

The formula I've been trying is not working

=IF(AND([End Date]@row >= TODAY(), Status@row <> "Complete, N/A"))


Best Answers

Answers

  • Kiela
    Kiela ✭✭✭✭

    I dont have a check box but instead a colour status and is based on % complete, if you remove the % below it might help

    Also I find Chat GPT good for putting in the scenario you want and it can give you the formula back as well

    =IF([Start Date]@row = "", "", IF([% Complete]@row = 1, "Blue", IF([Finish Date]@row < TODAY(), "Red", IF(AND([Finish Date]@row - TODAY() < 3, [% Complete]@row < 0.75), "Yellow", "Green"))))

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭
    Answer ✓

    Hi @Aspen26

    I think this formula should work.

    =IF(OR([End Date]@row < TODAY()), IF(AND(Status@row <> "Complete", Status@row <> "N/A"), 1, 0))

    I hope that helps.

    Matt

    Matt Johnson

    Sevan Technology

    Smartsheet Aligned Partner

  • Aspen26
    Aspen26 ✭✭
    Answer ✓

    Thank you both! I got it to work and exclude any blanks (end date hasn't been populated yet) using

    IF(AND([End Date]@row <> "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!