% Complete and Actual Duration are two of those fields that everyone uses, but few people actually understand the background calculations. Most of my students seem to assume that Actual Duration is the elapsed duration between the start of a task and the finish of a task. Well it is – most of the time. Sometimes it isn’t. Similarly, a lot of people I’ve worked with assume that % Complete is a measure of how much duration has been burned since the beginning of the task. Again, that’s how it’s calculated until those times when it’s not calculated like that.
Huh?
How can the same field be calculated two different ways? These are some of the riddles that I’ve had to work through of late as I work with end users who have grown up using another scheduling tool. As we work through the intricacies of scheduling in Microsoft Project, they keep hitting me up with all sorts of interesting questions about specific calculations – often times forcing me to go back and investigate knowledge I’ve always taken for granted.
This is just such a case – where once I begin picking at the pieces, the entire system becomes a bit more involved. Here we have a pretty basic question, “How does Microsoft Project roll up % Complete to the Project Summary level?” Once I started trying to explain it, I realized that I’d run into excellent blog fodder. So here goes…
What % Complete Means…
Well that’s pretty easy. At the task level, % Complete is calculated as follows:
% Complete = Actual Duration / (Actual Duration + Remaining Duration)
…which can also be stated as:
% Complete = Actual Duration / Duration
…And How It Rolls Up…
Now when you ask how it rolls up, that’s where things may or may not get less intuitive (depending on how well developed your intuition is). What Project does is to take the sum of all of the subtask actual durations and divide them by the sum of the subtask durations. The formula would look something like this: Sum(Actual Duration) / Sum(Duration).
So if you have three tasks with the following data:
Actual Duration | Duration |
10 | 20 |
5 | 20 |
0 | 20 |
….then Project will calculate the rollup as (10 + 5 +0)/(20 + 20 + 20), or 15/60, or 25%.
OK, fair enough. So the rollup of % Complete is not a measure of Elapsed Duration from the start of the project to our current Status Date. The number is really essentially an average of the subtasks.
…but what about milestones? If we use the formula above, then milestones having zero duration would have no impact on the overall project % Complete. This is patently untrue, as we can see in the following example:
It turns out that milestones count for 1% of the % Complete rollup. That’s a number that is independent of the number of milestones in your schedule, or the relative duration of the non-milestone tasks. If you have an unfinished milestone in your project, the highest you will ever reach is 99% on the project rollup. So the Sum(Actual Duration) / Sum(Duration) formula still stands, with the caveat that if you have one or more unfinished milestones in your project, that formula will result in 99%.
Actual Duration
What about Actual Duration though? For tasks without assignments, this is simple enough. Actual Duration is the elapsed duration from the start of the task to the last date on which work was booked (which can be displayed as the Stop field.) How does that roll up to a summary level though? Totally differently. At the summary level, the system uses the following equation:
% Complete X Duration
So Actual Duration at the Project Summary Level is more or less an averaged percent of the total project duration X project duration – or Sum (Actual Duration)/Sum (Duration) X Duration. Actual Duration at the summary level does not represent what most people would consider to be the idea of Elapsed Duration. This is a key concept in understanding basic project metrics.
(For those math nuts out there, note that rolled up Actual Duration doesn’t include any adjustment in % Complete for incomplete milestones. Hence, in the above example, the calculation is 100% X Duration, and not 99% X Duration.)
Elapsed Duration
If then you would like to see the actual elapsed duration of the entire project – which is what a lot of folks assume is actually represented by summary-level Actual Duration, then we can calculate that with a custom field using the following formula (in a spare Duration field):
IIf([Stop]=ProjDateValue(“NA”),0,ProjDateDiff([Start],[Stop]))
…making sure to set summary tasks to calculate using the formula.
In theory, if you’re updating your project correctly, the Elapsed Duration should then equal the same as the difference between the Project Start Date and the Project Status Date. (ProjDateDiff([Start],[Status Date])).
Then, if we want to calculate the % Elapsed Duration Complete – which would give you a rough measure of how much duration you’ve actually burned through:
IIf([Milestone]=Yes,0,Round((IIf([Stop]=ProjDateValue(“NA”),0,ProjDateDiff([Start],[Stop])))/[Duration]*100))
These calculations will give you the results as follows. Note how the summary row calculates differently between the custom formulas we just developed and the out of the box rollup calculations.
% Complete on Tasks with Assignments
So just to wrap up this topic….let’s make sure we also understand how % Complete and Actual Duration roll up from assignments at the task level….
On tasks with assignments, the Actual Duration rolls up on a daily basis as the total of Sum (Actual Work) / Sum (Work) for each day that work is booked. In the following example, 1 hour was worked out of 9 planned, giving us an Actual Duration of .11 days (1/9).
% Complete then is the calculation of that number divided by Duration – i.e. (Daily Total [Sum (Actual Work) / Sum (Work)] ) / Duration
So there you have it, clear as mud.
% Complete Rollup | Actual Duration Rollup | |
Project Summary | [Sum (Actual Duration) / Sum (Duration)] – 1% for incomplete milestones | [Sum (Actual Duration) / Sum (Duration)] X Duration |
Summary | [Sum (Actual Duration) / Sum (Duration)] – 1% for incomplete milestones | [Sum (Actual Duration) / Sum (Duration)] X Duration |
Task (w/o assignments) | Actual Duration / Duration | Stop – Start |
Task (w/ assignments) | Actual Duration / Duration | Daily Total [Sum(Actual Work)/Sum (Work)] |
Assignment | NA | NA |