Stop Light Formula Modifications

JiyaShah

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.

Genevieve P.

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