Referencing Status Codes by Color for At Risk Formula

Options

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!

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!