Stop Light Formula Modifications
Review the formula that sets the color of the stoplight to not have Yellow as 'on track"
we are using this current formula: =IF([Start Date]1 - TODAY() > 10, "", IF(AND([Start Date]1 - TODAY() <= 10, [Start Date]1 - TODAY() > 0), "Blue", IF(OR([% Complete]1 = 1, [Start Date]1 = TODAY()), "Green", IF(AND([End/Due Date]1 < TODAY(), [% Complete]1 < 1), "Red", IF(AND([Start Date]1 < TODAY(), IF(Duration1 = 0, 0, (100 / (Duration1 / NETWORKDAY([Start Date]1, TODAY()))) / 100) - [% Complete]1 <= 0), "Green", IF(AND([Start Date]1 < TODAY(), IF(Duration1 = 0, 0, (100 / (Duration1 / NETWORKDAY([Start Date]1, TODAY()))) / 100) - [% Complete]1 >= 0.25), "Red", "Yellow"))))))
Issue: Yellow represents on track (Project Status/Stop Light Status). This is confusing to our Internal Team and Client. We are looking to have Green stop light as On Track. Could you please help me define the formula that can work for us. I have included screenshot below. Please let me know if you have further questions. I can reached at jishah@hcg.com Thank you so much for your help.
Answers
-
Hi @JiyaShah
Can you show a screen capture of the rows where you see Yellow when it should be Green?
Based on the formula, you should see Green for any of the rows that are "on track", meaning they're in progress and they have the correct % complete based on where they are in the timeline.
This is determined by this part of the formula:
IF(AND([Start Date]1 < TODAY(), IF(Duration1 = 0, 0, (100 / (Duration1 / NETWORKDAY([Start Date]1, TODAY()))) / 100) - [% Complete]1 <= 0), "Green",
It says:
If the Start date is in the past AND the duration percent is less than or equal to the % in the % Complete column, return a Green status ball.
The reason you see Yellow in that top row is because even though the Task is "in progress", it's actually not "on track" based on the duration and percentage.
This task has 150 days duration, and you're 70 working days through the task (or 46% of the way through the task), but your % complete is set at 37% based on the Child Rows. That means it's not "on track" as something is delaying it's percent.
Does that make sense?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!