# Traffic light formula problem with two conditions

Options

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

• ✭✭✭✭✭✭
Options

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.

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"

• Options

Hi...yes RGY

• Employee Admin
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!