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
-
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.
☑️ 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...yes RGY
-
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
Categories
Check out the Formula Handbook template!