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

Options
✭✭✭
edited 04/19/24

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

• ✭✭✭✭✭✭
edited 04/21/24
Options

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

• ✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!