Referencing Status Codes by Color for At Risk Formula

SusieChoung

I would like to mark tasks with an "At Risk" check if we are within 3 days of the "End Date" and the "Status" is NOT green. The status column is currently as follows:

=IF(AND([End Date]@row < TODAY(), Complete@row = 0), "Red", IF(Complete@row = 1, "Green", "Gray"))

I've seen formulas to incorporate the "At Risk" check within a certain number of days of an end date, but based on a numerical percentage completed or text marked in progress or something to that effect. But don't know how to define "Green" or whether I need to repeat some part of the above.

Would love some help on the formula!

Kelly Moore

Hey @SusieChoung

You defined the colors correctly. When using colors or any of the other Symbol nomenclature you add them to an IF just as if they were another word you've seen in other examples - like 'In progress'.

The two [End Date] terms sandwich the criteria into the "are within 3 days of the "End Date"" criteria that you are looking for.

=IF(AND([End Date]@row >= TODAY(), [End Date]@row <= TODAY(3), Status@row="Green"), 1)

Will this work for you?

Kelly