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.
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...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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!