Traffic light formula problem with two conditions

Dear


I have a column of% Complete (% Complete) and a Task Completion (ENDDATE)

I would like to generate the following conditions:

1. Green if 100% of the task is complete and it has been given on the estimated date (which is equal to or less than today)

2. Yellow if it is between 51% and 99% complete as of today

3. Red if the advance is less than 50% and the date is today

Your help is greatly appreciated.

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Alfredo Alva 

    Hope you are fine, you want to do it in conditional formatting to highlight the hole row or you want to do it using a symbols column with RGY.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hi @Alfredo Alva


    Try the following formula:

    =IF([% Complete]@row = 1, "Green", IF(AND([% Complete]@row < 0.5, [ENDDATE]@row <= TODAY()), "Red", "Yellow"))


    These are the instructions:

    Green: IF([% Complete]@row = 1, "Green"

    If the % Complete is 100%, turn Green (regardless of End Date)


    Red: IF(AND([% Complete]@row <= 0.5, [ENDDATE]@row <= TODAY()), "Red"

    If the % Complete is less than or equal to 50%

    AND

    If the End Date is either today or in the past


    Yellow: Otherwise, Yellow.

    If the % Complete is not 100%, and not less than 50% with a date in the past

    (Aka - if the % is ongoing and the end date is in the future)


    Let me know if this returns the correct colours for your process!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!