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?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Yes, thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!