Creating and understanding a custom “Planned % Complete” field in Microsoft Project

Microsoft Project is a powerful, inexpensive tool, allowing users to efficiently plan, and track the progress of their projects. However, users often wonder why such a powerful tool omits a notable feature such as a built-in “Planned % Complete” field. The idea of a “Planned % Complete” field is to provide you with an indication of where you should be compared to where you currently are, at a specific point in time. Although there is no confirmed answer, the absence of this field can be attributed to the complexity and variability of projects making any “Planned % Complete” dynamic, and dependent on a myriad of factors unique to each project.

Before evaluating how to create a custom “Planned % Complete”, we need to understand the various ways in which Microsoft Project calculates percent complete. There are predefined fields to measure project progress in Microsoft Project; the most distinctive fields being % Complete, % Work Complete, and Physical % Complete.

One of Microsoft Project’s most common progress tracking fields is “% Complete”. Microsoft describes % Complete as the status of a task, expressed as the percentage of the task’s duration that has been completed. Duration is considered as the determining factor in this calculation.

EXAMPLE:

Microsoft Support provides you with a % Complete example (link below).

Microsoft Support – Percent Complete

Another way to track progress in Microsoft Project is the % Work Complete field. This field reflects the amount of actual work completed as a percentage of the total planned work and considers resources as the determining factor in this calculation.

EXAMPLE:

Microsoft Support provides you with a % Work Complete example (link below).

Microsoft Support – % Work Complete

The last percentage complete calculation to consider in Microsoft Project is Physical % Complete. Physical % Complete is also known as Earned Value % Complete taking Budgeted Cost of Work Performed (BCWP) into consideration, which contains the cumulative value of the task’s resources, or assignment’s percent complete multiplied by the time-phased baseline costs. BCWP is calculated up to the status date of your schedule.

EXAMPLE:

Microsoft Support provides you with a % Work Complete example (link below).

Physical % Complete

Understanding how Microsoft Project calculates each percentage complete field essentially guides you in the development of your custom planned percentage.

It is also essential to consider your schedule configurations prior to developing your schedule, as this determines how Microsoft Project calculates your schedule once you baseline and start tracking progress. For example, when tracking % Complete (Duration), your schedule options should be set to Auto Scheduled, Fixed Duration, and % Complete. Schedule configuration is the topic of a separate paper, to be published soon.

In our mission to create a Custom Planned % Complete field, an important step is to create a custom field. Let’s look at this next.

To create a custom field in Microsoft Project, select the Project tab, and select Custom Fields.

There are several customisable field types, each providing code values that can be used to create custom fields with varying results:

To create a custom “Planned % Complete”, we will use Task Field and Text Type custom fields.

Select a text to modify (Any available text field from Text 1 to Text 20), select Rename, and type in “Planned % Complete” in the dialogue box. Click OK.

Go to the Custom attributes selections and select Formula.

In the formula dialogue box, there are various selections to assist you in building your formula. The Function dropdown provides you with a vast range of functions. The Field drop down provides you with existing and custom fields you can use in your formula.

To create the formula, you can use a function or, should there be more than one criterion to be met, multiple functions. For example, select the Function drop down, select General, and select IIf(expression,truepart,falsepart).

The Expression is the condition you want to evaluate. The TruePart is the value the function returns if the function evaluates to true. And the FalsePart is the value that the function returns if the function evaluates to false.

Below you will find the “Planned % Complete” formula which can be copied and pasted into the formula dialogue box.

IIf([Baseline Start]=ProjDateValue(“NA”),0 & “%”,IIf([Status Date]=ProjDateValue(“NA”),0 & “%”,IIf([Status Date]<=[Baseline Start],0 & “%”,IIf([Status Date]>=[Baseline Finish],100 & “%”,CStr(CInt(IIf(ProjDateDiff([Baseline Start],[Status Date])/480>=Val(ProjDurConv([Baseline Duration],pjDays)),Val(ProjDurConv([Baseline Duration],pjDays)),IIf(DateDiff(“d”,[Baseline Start],[Status Date])<=0,0,ProjDateDiff([Baseline Start],[Status Date])/480))/Val(ProjDurConv([Baseline Duration],pjDays))*100)) & “%”))))

When you have pasted the formula, click OK. When you receive the following pop-up dialogue box, click OK again.

Go to the Calculation for task and group summary rows and click on Use formula. This selection ensures that the formulas are rolled up to group summary rows.

The formula evaluates the relationship between the Baseline Start, Status Date, Baseline Finish, and Baseline Duration. Although it seems quite complex, breaking the formula down will make it easier to understand.

Let’s break down the formula step-by-step:

To add the newly created custom field to your Entry or Tracking table, select any column in the table, right-click and select Insert Column. Type “Planned % Complete” and select the “Planned % Complete” custom field.

REQUIREMENTS FOR THE FORMULA TO WORK

As the “Planned % Complete” formula evaluates the relationship between the Baseline Start, Status Date, Baseline Finish, and Baseline Duration, there are 2 requirements to ensure the custom formula calculates.

The first requirement to making the formula work is setting the baseline. To set the project baseline, click on the Project tab, click on Set Baseline, and click OK.

The second requirement is to update the Status Date.

To update the project Status Date, select the Project tab, click on Status Date, and select a date from the calendar. Click OK.

You will now be able to track “Planned % Complete” and compare it to actual project progress.

To enhance progress reporting, you can add Planned vs. Actual indicators on your schedule, providing you with progress indicators at a quick glance. Schedule indicators are addressed in a separate paper that will be published soon.

You may encounter an error when trying to create a custom formula. The most common error relates to the decimal and list separator characters which are dependent on your computer settings.

This means that your formula separator and your decimal separator are the same. For example, if you use a comma (,) as a list separator, ideally your decimal separator should be a period (.).

Formula: IIF(expression,truepart,falsepart)

Formula Result (based on your computer settings): 100,00%

You will receive the error message as your list separator in your formula is a comma (,) and your decimal separator is a comma (,).

To fix this issue, configure your computer Region (Change date, time, or number format) settings to ensure that the Decimal symbol reflects a period (.) and the list separator reflects a comma (,)

Creating a custom “Planned % Complete” field in Microsoft Project allows users to better track project progress against planned milestones, providing a more dynamic and personalized approach to project management.

To create a custom “Planned % Complete”, follow these steps:

• Open Microsoft Project > Navigate to the Project Tab > Click on Custom Fields.
• Select the Text Type Custom Field > Select an available Text field [Text 1].
• Rename the Custom Field to “Planned % Complete”.
• Copy & paste the formula for “Planned % Complete”.

IIf([Baseline Start]=ProjDateValue(“NA”),0 & “%”,IIf([Status Date]=ProjDateValue(“NA”),0 & “%”,IIf([Status Date]<=[Baseline Start],0 & “%”,IIf([Status Date]>=[Baseline Finish],100 & “%”,CStr(CInt(IIf(ProjDateDiff([Baseline Start],[Status Date])/480>=Val(ProjDurConv([Baseline Duration],pjDays)),Val(ProjDurConv([Baseline Duration],pjDays)),IIf(DateDiff(“d”,[Baseline Start],[Status Date])<=0,0,ProjDateDiff([Baseline Start],[Status Date])/480))/Val(ProjDurConv([Baseline Duration],pjDays))*100)) & “%”))))

• Select Use Formula to roll up the calculation to task and summary rows.
• Select a column in your Entry / Tracking table, right-click and select Insert Column. Insert the “Planned % Complete” field.
• Click on the Project tab, select Set Baseline, and click OK
• Click on your Project tab, select the Status Date calendar, select a date, and click OK.

By understanding and utilizing custom fields, project managers can gain more accurate insights into their projects’ progress, enhancing their ability to manage timelines effectively.