Help with formula in Project Office Management template set

Mike Haddock

Hello,

I'm using the Project Management Office template set and one of the default formulas appears to calculate the schedule delta with the following formula - =IFERROR([Schedule Delta (Working Days)]@row / Duration@row, "")

Can someone help me understand how it's calculating? I would expect the status of the Schedule Delta % and therefore the task status to be red if the task hasn't started or isn't complete and the target end date is past days date.

Is that a correct assumption?

Genevieve P.

Hi @Mike Haddock

This formula looks at the column Schedule Delta (Working Days), takes that number and divides it by the Duration column in that row. If there's an error (ex. if there is no data to divide by), then it returns blank.

If the task hasn't started or isn't complete, and the target date is in the past, then the Schedule Delta (Working Days) will show how many additional days were used for this task, and the % will show you how much of an increase this task took.

For example, I've highlighted two rows that have one additional day in the End Date column (versus the Target End Date).

In the task where the original duration was 1 day, this extra day is a 50% increase in the length of the task. For the row above with an original 11 day duration, adding one extra day to complete the task is only an 8% increase in length:

I hope that helps!

Cheers,

Genevieve

Roh12

Hi @Genevieve P.

I use a similar formula to the one mentioned above to determine the project's health using the baseline finish, the actual end date, and the completion percentage. Could you kindly elaborate on the Schedule Delta (Working Days) and Schedule Delta (%) formulas? I'm intrigued to know how they affect projects so that I may make adjustments to meet our demands.

Genevieve P.

Hi @Roh12

The sheet and formula above can be found in the Project Office Management template set 🙂

The formula in the "Schedule Delta (%)" column is:

=IFERROR([Schedule Delta (Working Days)]@row / Duration@row, "")

The formula in the "Schedule Delta (Working Days)" column is:

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

The Baseline feature is a newer release that came out after this post and template were created; the Baseline Variance should give you a similar picture.

Cheers,

Genevieve