Start Dates Are Earlier than Target Dates
We have a project that was pushed up on the timeline so the target dates are later than the actual dates. This is causing a negative Schedule Delta & a negative Schedule Delta (working days) due to the formulas. Is there a change I should make to the formula to reflect that this work is actually on time since it's before the targeted dates? This is causing our schedule health formula to turn red when in reality it should be showing green.
Schedule Health Formula: =IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))
Schedule Delta (%) Formula: =IFERROR([Schedule Delta (Working Days)]@row / Duration@row, "")
Schedule Delta (Working Days) Formula: =IFERROR(IF([End Date]@row = [Target End Date]@row, 0, IF([End Date]@row > [Target End Date]@row, NETWORKDAYS([Target End Date]@row, [End Date]@row) - 1, IF([End Date]@row < [Target End Date]@row, NETWORKDAYS([Target End Date]@row, [End Date]@row) + 1, ""))), "")
Thank you!
Best Answer
-
Hi @Sarah123
The status is Red because the cell in the [Schedule Delta (%)] for that row is blank (which is less than 10%).
If you'd like this to be a blank cell instead of having a status indicator, you can add another IF statement to your formula:
=IF([Schedule Delta (%)]@row = "", "", IF([Schedule Delta (%)]@row < 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green")))
You can of course have this be a different colour... such as Green, if that's what you'd prefer for blank cells:
=IF([Schedule Delta (%)]@row = "", "Green", IF([Schedule Delta (%)]@row < 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green")))
Does that make sense?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @Sarah123
Your first formula returns "Red" when the Schedule Delta (%) is greater than 10%. This means that even if you're at 90%, it will show Red. I believe you may mean less than 10%? If so, simply swap around the > to be < and you should be good to go!
=IF([Schedule Delta (%)]@row < 0.1, "Red",
Full Schedule Health Formula:
=IF([Schedule Delta (%)]@row < 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))
Then for your negative percents, we'll need to adjust your third formula (which will update your second one automatically).
Right now you're looking for the working days between the Target End Date and the End Date. However you're setting the Target date as the "Start Date" when we know it's in the future from the actual End Date (meaning you'll always have a negative number).
Instead, swap around the placement of these two references within the NETWORKDAYS.
Original:
IF([End Date]@row < [Target End Date]@row, NETWORKDAYS([Target End Date]@row, [End Date]@row) + 1,
Adjusted:
IF([End Date]@row < [Target End Date]@row, NETWORKDAYS([End Date]@row, [Target End Date]@row) + 1,
You will now have a positive percent if the actual end date is earlier than the Target end date. I would actually suggest reversing this same structure for your previous IF statement as well (when the actual end date is past the target date). That will give you a negative number, therefore a negative percent, and a red status ball.
Schedule Delta (Working Days) Formula:
=IFERROR(IF([End Date]@row = [Target End Date]@row, 0, IF([End Date]@row > [Target End Date]@row, NETWORKDAYS([End Date]@row, [Target End Date]@row) - 1, IF([End Date]@row < [Target End Date]@row, NETWORKDAYS([End Date]@row, [Target End Date]@row) + 1, ""))), "")
Let me know if I can clarify anything further or if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I'm not sure if I'm missing something. I updated the formulas to what you recommended and now everything is showing red.
Schedule Health: =IF([Schedule Delta (%)]@row < 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))
Schedule Delta (working days): =IFERROR(IF([End Date]@row = [Target End Date]@row, 0, IF([End Date]@row > [Target End Date]@row, NETWORKDAYS([End Date]@row, [Target End Date]@row) - 1, IF([End Date]@row < [Target End Date]@row, NETWORKDAYS([End Date]@row, [Target End Date]@row) + 1, ""))), "")
-
Hi @Sarah123
The status is Red because the cell in the [Schedule Delta (%)] for that row is blank (which is less than 10%).
If you'd like this to be a blank cell instead of having a status indicator, you can add another IF statement to your formula:
=IF([Schedule Delta (%)]@row = "", "", IF([Schedule Delta (%)]@row < 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green")))
You can of course have this be a different colour... such as Green, if that's what you'd prefer for blank cells:
=IF([Schedule Delta (%)]@row = "", "Green", IF([Schedule Delta (%)]@row < 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green")))
Does that make sense?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Yes, thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 136 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 485 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!