Automate RYG For Project Tasks

David_Peng

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. 

Mike Wilday

You kind find and adapt the final formula found in this post about RYG automation.

David_Peng

Thanks Mike, very helpful link you've shared. 

Mike Wilday

You're welcome. Let me know if you need further help and I'll see what I can do. 

David_Peng

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

Mike Wilday

Great. Glad I could point you in the right direction. 

David_Peng

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? 

Mike Wilday

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

David_Peng

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

Robert S.

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

Giancarlo

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?

Andrée Starå

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

Joe Haney

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

Andrée Starå

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!

Joe Haney

Thanks Andree, I was able to get it to work.

Andrée Starå

@Joe Haney

Happy to help!

Glad that you figured it out!