RGYB based on Task Status

Joseph Aloysias

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

Mike TV

"within range" is?

"exceeds range" is?

Joseph Aloysias

"within range" is? & "exceeds range" is?- duration between start date & End date

Genevieve P.

Hi @Josephcopernicus

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