RYGG ball and task status formula based on % complete and due date
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"))))
Answers
-
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
-
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?
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!