RYGG ball and task status formula based on % complete and due date

Emily Miller

Hello!

I am working on a column formula for a project plan where the RYGG ball and Task Status will automatically be updated based on the % complete and the due date. What I'm trying to solve for is:

• if % complete = 0 and start date is today or in the future, then stage is grey and status is "not started"
• if % complete = 1-99 and due date is today or in the future, then stage is green and status is "in progress"
• if % complete = 0-99 and due date is in the past, then stage is red and status is "late"
• if % complete = 100 then stage is green and status is "completed"

Here is the formula I've been working on. I'm almost there, or at least I got to unparseable. What am I missing? Any help would be appreciated!

=IF(COUNT(ANCESTORS([Task Name]@row)) = 0, IF(AND(Start@row = "", Finish@row = ""), "Gray", IF(AND([% Complete]@row < 0.5, Finish@row < TODAY(30)), "Red", IF(AND([% Complete]@row < 0.75, Finish@row < TODAY(30)), "Yellow", IF(OR([% Complete]@row > 0.75, Finish@row >= TODAY(30)), "Green"))))), IF(OR(Stage@row="On Hold", Status@row="Cancelled"),"Gray", IF(AND(Finish@row="", Stage@row=""),"", IF(OR(Stage@row="Complete",Finish@row > TODAY(7)),"Green", IF(TODAY(), Finish@row > 0),"Red",IF(OR(Stage@row="Not Started",Status@row="In Progress",Stage@row=""),"Yellow"))))

Kelly Moore

Hey @Emily Miller

For clarification, are you trying to append your 'problems to solve' onto the existing formula that references your top row parent (Hierarchy Level = 0)? In other words, if the level = 0, do the existing formula up to the point where the parentheses close, otherwise if Level>0, add new criteria? If yes, is the new criteria applicable to all rows where level>0, or only Level>0 and is also a parent row (2nd row in screenshot)?

I ask as I notice your 'problems to solve' do not mention hierarchy level as part of the criteria

Kelly

Emily Miller

Hi @Kelly Moore,

What I'm trying to solve for are the items listed above. Hierarchy doesn't factor into the problem we're trying to solve for. We're trying to use formulas so that when a task is 0% and the start date is today or in the future, then the health ball in the "Stage" column automatically is grey and the listed status in the "Status" column is "not started", for example.

The same would go with the other items we're solving for:

If the task is somwhere between 1 and 99% complete and the due date is today or in the future, we want the health ball in the Stage column to automatically be populated green and the status in the Status column to be "In Progress." etc etc.

Does that help clarify?

Kelly Moore

Hey @Emily Miller

Below are the formulas you requested.

Status column

=IF([% Complete]@row = 1, "Green", IF(AND([% Complete]@row = 0, Start@row >= TODAY()), "Gray", IF(AND([% Complete]@row <= 0.99, Finish@row >= TODAY()), "Green", IF(AND([% Complete]@row <= 0.99, Finish@row < TODAY()), "Red"))))

Stage column

=IF([% Complete]@row = 1, "Completed", IF(AND([% Complete]@row = 0, Start@row >= TODAY()), "Not Started", IF(AND([% Complete]@row <= 0.99, Finish@row >= TODAY()), "In Progress", IF(AND([% Complete]@row <= 0.99, Finish@row < TODAY()), "Late"))))

Will this work for you?

Kelly