Percent plan in Microsoft Project – Part 1

Recurrently in my consulting and training work on Microsoft Project, emerges the question of how to calculate the percentage planned progress of tasks.

In this article, I will start providing an answer.

Note: The concepts and formulas in this article work well for the 2010, 2013, 2016 versions of Microsoft Project.

The formula for planned advance

The question has its origin in the Microsoft Project includes a field called % Complete which represents the actual progress of a task up to the status date, however, there is no a field that represents the planned progress.

In fact, you can get the earned value and the planned value of the project tasks if you use the Earned Value Method. The problems is that, even when the EVM is great and deserves our attention –we will blog about it for sure –, it is not easy enough for everyone and for every project. That is why we need to talk about an easier alternative.

The customized field % Plan will permit us to compare the actual progress, % Complete, with the planned progress and thus obtain performance indicators for tasks, deliverables, phases and projects.

So, we would like to have the following fields for the tasks:

Task Name	% Plan	% Complete	% Progress variation
Task 1	45 %	40 %	-5 %
Task 2	20 %	25 %	5 %

Therefore, we will define a custom field to set the % Plan.

Let us use the customizable field Number 1 and call it % Plan num.

We define custom field formula:

IIf([Baseline Start]<91313,IIf(datediff(“d”,[Status Date],[Baseline Start])<=0,IIf(datediff(“d”,[Status Date],[Baseline Finish])>0,((Projdatediff([Baseline Start],[Status Date])/([Baseline Duration]))*100),100),0),0)

 

Explanation of the formula

In the following examples:

• I am using the tracking Gantt view.
• The status date is Friday, June 17, 2016 and it is represented by the vertical red solid line.
• The % Complete is represented at the right of the bar in the Gantt chart.
• The % Plan is represented in the column % Plan num.

Let me explain each part of the formula:

Task without baseline

IIf([Baseline Start]<91313,IIf(datediff(“d”,[Status Date],[Baseline Start])<=0,IIf(datediff(“d”,[Status Date],[Baseline Finish])>0,((Projdatediff([Baseline Start],[Status Date])/([Baseline Duration]))*100),100),0),0)

The part of the formula marked in bold font verifies that the task baseline exist. If there is not baseline then the result of the formula is 0.

The 91313 represents the numeric value of the Dec 31, 2149 date in MS Project, which is the maximum date that can be stored in a Project date field. If the date field is not defined, then Project fills the field with 1s at bit level, given a number long beyond 91313. When this happens, the field shows the NA value on the screen or printout.

Example: Task without baseline: % Plan = 0

 

Task with baseline starting after the status date

IIf([Baseline Start]<91313,IIf(datediff(“d”,[Status Date],[Baseline Start])<=0,IIf(datediff(“d”,[Status Date],[Baseline Finish])>0,((Projdatediff([Baseline Start],[Status Date])/([Baseline Duration]))*100),100),0),0)

 This section of the formula is there to review if the task baseline started before the status date, otherwise, that is, if baseline will start after the status date, then the % Plan is 0.

Example: Task with baseline starting after the status date: % Plan = 0

 

Task whose baseline finished before the status date

IIf([Baseline Start]<91313,IIf(datediff(“d”,[Status Date],[Baseline Start])<=0,IIf(datediff(“d”,[Status Date],[Baseline Finish])>0,((Projdatediff([Baseline Start],[Status Date])/([Baseline Duration]))*100),100),0),0)

 

If the baseline finished before the status date, then the % Plan is 100.

Example: Task with baseline that finished before the status date: % planned = 100

Task with baseline is in progress at status date

IIf([Baseline Start]<91313,IIf(datediff(“d”,[Status Date],[Baseline Start])<=0,IIf(datediff(“d”,[Status Date],[Baseline Finish])>0,((Projdatediff([Baseline Start],[Status Date])/([Baseline Duration]))*100),100),0),0)

This part of the formula calculates the % Plan when the baseline starts before the status date and ends after or at the status date.

Example: Task baseline in process: % Plan = 50

 

Conditions to the % Plan

To obtain the % Plan for the tasks on your project, the following conditions apply:

1. The baseline must be generated.
2. The status date must be defined.
3. Add the formula to a number custom field.

 

Given this definition, we could complement our model in MS Project with the following aspects.

• Set a custom text field whose value is equal to the field % Plan num and format it as a percentage.
• Use another custom field to compare the % Plan against the % Complete and generate an indicator of the variation between real progress and planned progress.
• The formula of % Plan could be further adapted to consider the cases of inactive tasks and tasks with manual programming.
• We could generate a full model and a Microsoft Project template based on the concept of the % planned.

I will further develop these ideas in following blog entries.

 

In the meantime, please take into consideration that the proposed formula for % Plan is based on the duration of the baseline and therefore applies very well in tasks with a uniform distribution of work.

This formula also works well to summary tasks providing that the corresponding detail tasks are uniformly distributed in time, otherwise the % Plan is distorted compared to % Complete in summary tasks. I will show you how to overcome this issue in a future article.

 

More articles on the subject:
Percent Plan in Microsoft Project – Part 2

Percent Plan in MIcrosoft Project – Part 3

 

On the following page you can find information of a robust and complete version of the model:
Percent Plan Model for Microsoft Project

 

That is all for now.

Tanks for reading and… Have a happy day!