Manually Adjust Automated Logic - Status Colors

MMorgan

Hello,

I have a full project list that I've added a formula for project status for red, yellow, green, and blue. These are all linked to the phase of the project and the start or finish date. The only issue I have is if the project is known to be at risk or failure but the date is still in the future. These need to have a manual override to change the status. If I do this, I lose the trigger for the formula automation. Is there a workaround? The only thing I can think of is creating a separate column with a checkbox for at risk and known failure and add that logic to my current formula. Is there anything else that is recommended? Any guidance is appreciated, thank you.

Paul Newcome

You would add them to the beginning as additional nested IFs.

=IF([At Risk/ Off Track = "AR", "Yellow", IF([At Risk/ Off Track]@row = "OT", "Red", rest of nested IFs)

Michelle Choate 2

How do you currently denote them if they are at risk? My recommendation would be to create a flag column and add that into your formula, as you have mentioned above.

MMorgan

Thank you. For reference, here are the current rules for what I'm wanting to automate and the formula:

In Progress - late = red
In Progress - on time = green
Planning, To Do, Backlog - past start = yellow

To Do, Backlog - no date = blue

On Hold - no date = red
On Hold - Future end Date = green
Planning - future date = blue

=IF(AND([End Date]@row > TODAY(), OR(Status@row = "In Progress", Status@row = "On Hold")), "Green", IF(AND(AND([Start Date]@row <> "", [Start Date]@row <= TODAY()), OR(Status@row = "To Do", Status@row = "Backlog", Status@row = "Planning")), "Yellow", IF(AND([End Date]@row < TODAY(), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND(OR([End Date]@row = "", [Start Date]@row = ""), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND([Start Date]@row = "", OR(Status@row = "To Do", Status@row = "Backlog")), "Blue", IF(AND([Start Date]@row > TODAY(), OR(Status@row = "Planning")), "Blue"))))))

What I want to add are the two options to override the above options. I did create a new column that has the dropdown option of AR = At Risk and OT = Off Track

=IF([At Risk/ Off Track]@row = "AR", "Yellow")

=IF([At Risk/ Off Track]@row = "OT", "Red")

I can get each to work individually, but not sure how to add into the initial formal.

I'm happy to schedule time if needed or no one else can answer. Thanks!

Paul Newcome

You would add them to the beginning as additional nested IFs.

=IF([At Risk/ Off Track = "AR", "Yellow", IF([At Risk/ Off Track]@row = "OT", "Red", rest of nested IFs)

MMorgan

YES! thank you! I was trying to add them to each of the nested color option. This is perfect! Thank you

Paul Newcome

Happy to help. 👍️