Start Dates Are Earlier than Target Dates

Sarah123

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!

Genevieve P.

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?

Genevieve P.

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

Sarah123

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, ""))), "")

Genevieve P.

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?

Sarah123

Yes, thank you!