Traffic light formula problem with two conditions

Alfredo Alva

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.

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.

Alfredo Alva

Hi...yes RGY

Genevieve P.

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