How do I Automate the At Risk Column?

Aspen26

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"))

Matt Johnson

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

Aspen26

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 <> "")

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

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

Aspen26

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 <> "")