Traffic light formula problem with two conditions



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.


  • 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

    ☑️ 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"

  • Alfredo Alva
  • Genevieve P.
    Genevieve P. Employee Admin

    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%


    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!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!