RGYB based on Task Status
![Joseph Aloysias](https://us.v-cdn.net/6031209/uploads/userpics/WZDEOXSJITB8/nDP4L5HRUUJSS.jpg)
Hi,
We have a formula to calculate RGY based on Start date, End date and % complete : =IF([Target Start Date]@row > TODAY(), "Yellow", IF(AND([% Complete]@row < 1, [Target End Date]@row < TODAY()), "Red", "Green"))
Additionally, would like to change the color based Task status (Not Started, In Progress, Completed)
if the start date , end date within range and % complete below 100%, Task status=in progress: Yellow
if the start date , end date within range and % complete 100%, Task status=Completed: Green
if the start date , end date within range and % complete below 100%, Task status=Not Started: Blue
if the start date , end date exceeds range and % complete below 100%, Task status=Not Started/ In progress: RED
Answers
-
"within range" is?
"exceeds range" is?
-
"within range" is? & "exceeds range" is?- duration between start date & End date
-
We can write statements that match exactly what you listed:
if the start date , end date within range and % complete below 100%, Task status=in progress: Yellow
IF(AND([Target Start Date]@row > TODAY(), [Target End Date]@row < TODAY(), [Task Status]@row = "In Progress"), "Yellow"
if the start date , end date within range and % complete 100%, Task status=Completed: Green
IF(AND([Target Start Date]@row > TODAY(), [Target End Date]@row < TODAY(), [Task Status]@row = "Completed", [% Complete]@row = 1), "Green"
if the start date , end date within range and % complete below 100%, Task status=Not Started: Blue
IF(AND([Target Start Date]@row > TODAY(), [Target End Date]@row < TODAY(), [Task Status]@row = "Not Started", [% Complete]@row < 1), "Blue"
if the start date , end date exceeds range and % complete below 100%, Task status=Not Started/ In progress: RED
IF(AND([Target End Date]@row < TODAY(), [% Complete]@row < 1, OR([Task Status]@row = "Not Started", [Task Status]@row = "In Progress")), "Red"
Full Formula based on your criteria:
IF(AND([Target Start Date]@row > TODAY(), [Target End Date]@row < TODAY(), [Task Status]@row = "In Progress"), "Yellow", IF(AND([Target Start Date]@row > TODAY(), [Target End Date]@row < TODAY(), [Task Status]@row = "Completed", [% Complete]@row = 1), "Green", IF(AND([Target Start Date]@row > TODAY(), [Target End Date]@row < TODAY(), [Task Status]@row = "Not Started", [% Complete]@row < 1), "Blue", IF(AND([Target End Date]@row < TODAY(), [% Complete]@row < 1, OR([Task Status]@row = "Not Started", [Task Status]@row = "In Progress")), "Red"))))
However this does not take into account any tasks that have an End Date in the past that is Complete. I agree with @Mike TV that the note about the dates being "in range" is a little confusing. Are you sure that matters for all of your instructions? Is it possible that this only matters for the tasks that are currently In Progress?
Try this instead:
=IF(AND([Target End Date]@row > TODAY(), [% Complete]@row < 1, OR([Task Status]@row = "Not Started", [Task Status]@row = "In Progress")), "Red", IF(AND([Task Status]@row = "Not Started", [Target End Date]@row < TODAY()), "Blue", IF(AND([Target Start Date]@row > TODAY(), [Target End Date]@row < TODAY(), [Task Status]@row = "In Progress"), "Yellow", IF(AND([Task Status]@row = "Completed", [% Complete]@row = 1), "Green", "Yellow"))))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!