RGYB based on Task Status

Joseph Aloysias
Joseph Aloysias ✭✭✭✭✭
edited 11/23/22 in Formulas and Functions

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

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    "within range" is?

    "exceeds range" is?

  • Joseph Aloysias
    Joseph Aloysias ✭✭✭✭✭

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!