Automate RYG For Project Tasks
Hi All
i am very new to SmartSheet. I am working on the project template which can help to highlight some tasks that have been overdue with RYG. Need your kindly help on the formula...
1. If task status is complete: Green
2. If the task status is Not Started: 1. Scheduled End Date is behind today: Grey. 2. Scheduled End Date is before Today: Red
3. If the task status is Pending: 1. Scheduled End Date is behind today: Yellow. 2. Scheduled End Date is before Today: Red
4. If the task status is In Progress: 1. Scheduled End Date is behind today: Green. 2. Scheduled End Date is before Today: Red
And also for the parent task: if any subtask status is Red, it will be red. Or if any subtask status is Yellow, it will be yellow, otherwise it will be green.
Really appreciate for any comments, thanks in advance.
Comments
-
You kind find and adapt the final formula found in this post about RYG automation.
-
Thanks Mike, very helpful link you've shared.
-
You're welcome. Let me know if you need further help and I'll see what I can do.
-
Just let you know i have figured out how to do it. Following is the formula i use, it looks working well.
=IF(AND(TODAY() > [End Date]1, Status1 = "In Progress"), "Red", IF(AND(TODAY() > [End Date]1, Status1 = "Not Started"), "Red", IF(AND(TODAY() > [End Date]1, Status1 = "Pending"), "Red", IF(OR(Status1 = "Complete", Status1 = "In Progress"), "Green", IF(Status1 = "Pending", "Yellow", IF(Status1 = "Not Started", "Gray"))))))
-
Great. Glad I could point you in the right direction.
-
Another question from my side is, how we can let the 'Overall Project status' be 'Red' or 'Yellow' if any tasks' status is 'Red' or 'Yellow', otherwise it will be green?
-
Check out this post. This post will give you the status of Red if any of the children are red, etc.
https://community.smartsheet.com/discussion/automating-ryg-and-children-formula
-
Great, it works...
=IF(COUNTIF([Health Status]1:[Health Status]34, "Red") > 0, "Red", IF(COUNTIF([Health Status]1:[Health Status]34, "YELLOW") > 0, "YELLOW", IF(COUNTIF([Health Status]1:[Health Status]34, "Green") > 0, "Green")))
-
Hello David,
Thanks for the question. In looking at your formula, it looks like there are some repetitive statements that could be made shorter . As well, you could use @row to make the formula a bit more efficient. This can help with performance if there's a large number of these formulas on this sheet. Here's our help center article with more information on this (https://help.smartsheet.com/articles/2476491#row).
Here's an example of how this formula can both be shortened slightly, and use @row:
=IF(AND(TODAY() > [End Date]@row, OR(Status@row = "In Progress", Status@row = "Not Started", Status@row = "Pending")), "Red", IF(OR(Status@row = "Complete", Status@row = "In Progress"), "Green", IF(Status@row = "Pending", "Yellow", IF(Status@row = "Not Started", "Gray"))))
As for you other formula for the overall status, currently this is set to only look at rows 1 through 34. If you add new rows to the sheet, they may not be automatically added to this calculation. If you'd prefer, you can instead reference entire columns so that even new rows will be used. Here's how that would look:
=IF(COUNTIF([Health Status]:[Health Status], "Red") > 0, "Red", IF(COUNTIF([Health Status]:[Health Status], "Yellow") > 0, "Yellow", IF(COUNTIF([Health Status]:[Health Status], "Green") > 0, "Green")))
More on the different reference types can be found here (https://help.smartsheet.com/articles/2476171#reference).
-
Hello All, I applied the formula shared above and worked perfectly, I am trying to work around having a red when the dates are still blank since i will be populating those as the project rolls or they will be populated by predecessors. any ideas?
-
Hi Giancarlo,
Try something like this.
=IF(OR(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "", IF(COUNTIF([Health Status]:[Health Status], "Red") > 0, "Red", IF(COUNTIF([Health Status]:[Health Status], "Yellow") > 0, "Yellow", IF(COUNTIF([Health Status]:[Health Status], "Green") > 0, "Green")))
Did it work?
Hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hello community,
I have a setup where my RYG status depends on the entries of 4 columns, each of which have 3 possible answers. What is the easiest way to construct the formula without having a million nested IF's (if possible)?
Thanks,
Joe
-
Hi @Joe Haney
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks Andree, I was able to get it to work.
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!