MS Project Actual vs Planned % Complete

Posted by

In MS Project there is no default field available to calculate the Planned % complete or the Baseline % completed. In this tutorial I will explain how to create a customized field to capture planned % complete to compare it against the actual % complete.

STEP 01Calculate the duration in days.

MS Project stores the duration in minutes, so in order to use the duration in days in our calculations create a customized number field and name it as “Duration in Days”.

How to do it: In MS Project, right click any column and select insert column. Then select field name as ‘Number 1′ and assign title as “Duration in Days”. Then right click the newly inserted column and select ‘Customize Fields’. On the custom attributes click on the radio button ‘Formula’ and type in the formula as below. In the next section click ‘Use Formula’ and then click OK.

FormulaVal(ProjDurConv(Duration, pjdays)) 

Syntax: ProjDurConv( expression, durationunits )

Formula Explanation: ProjDurConv is an available function in MS Project to convert the duration in to Days(pjdays) or Hours(pjhours) or minutes or in any other available format.

STEP 02Calculate the Elapsed days.

Based on the start date of the project and the  status date, calculate the days elapsed. This is required to calculate the percentage.

How to do it: In MS Project, right click any column and select insert column. Then select field name as ‘Number 2′ and assign title as “Elapsed Days”. Then right click the newly inserted column and select ‘Customize Fields’. On the custom attributes click on the radio button ‘Formula’ and type in the formula as below. In the next section click ‘Use Formula’ and then click OK.

FormulaIIf(ProjDateDiff(Start,[Status Date])/480>=Val(ProjDurConv([Baseline Duration],pjDays)),Val(ProjDurConv([Baseline Duration],pjDays)),IIf(DateDiff(“d”,[Baseline Start],[Status Date])<=0,0,ProjDateDiff(Start,[Status Date])/480))

SyntaxIIf( exprtruepartfalsepart )

Formula Explanation: If the Project date difference between the project start date and status date is >= duration means that duration for the task completion is over and hence return the project duration. If the condition is false, it means that the duration is still not completed and we need to compute the elapsed days. But if the elapsed working days is 0 or negative (which means the planned task is not yet started) we need to set the elapsed days as 0. Hence you see another ‘If’ condition in the false part of the first ‘If’ condition.

Note:  I updated the Formula to replace DateDiff function with ProjDateDiff as I was getting lot of queries from users that the formula considered weekends also in to calculations.  Also on the formula window click the option “Roll up to summary level” to get the correct percentages for the summary tasks as well.

STEP 03Calculate the Planned Percentage complete.

Planned % complete is calculated based on the ‘Elapsed Days’ (Number 2) and ‘Duration in Days’ (Number 1) fields created in above steps.

How to do it: In MS Project, right click any column and select insert column. Then select field name as ‘Number 3′ and assign title as “Planned Percent”. Then right click the newly inserted column and select ‘Customize Fields’. On the custom attributes click on the radio button ‘Formula’ and type in the formula as below. In the next section click ‘Use Formula’ and then click OK.

FormulaNumber2/Number1

SyntaxDivision (Math)

Formula Explanation: Dividing the Elapsed days by Duration of the task to get the percentage of completion for the task.

STEP 04Format the Percentage complete column.

To display the percentage complete field as text with a % sign, create a new text field column and in the formula format the Number3 field.

How to do it: In MS Project, right click any column and select insert column. Then select field name as ‘Text 1′ and assign title as “Planned % Complete”. Then right click the newly inserted column and select ‘Customize Fields’. On the custom attributes click on the radio button ‘Formula’ and type in the formula as below. In the next section click ‘Use Formula’ and then click OK.

Formula: Format(Number3, “0%”)

SyntaxFormat( expression[, format[, firstdayofweek[, firstweekofyear]]] ) all in ‘[‘ are optional..

Formula Explanation: Format the Number3 field as text and adds % sign at the end.

Hope this helps and let me know other ways of arriving at the planned % complete.

Subscribe to our mailing list

* indicates required



84 comments

  1. if you are using hours units instead of days, which parameters do you change in the second formula?. I just replaced days with hours and my calculation are not correct

  2. Why yke the planned percent doesn’t same with the % complete that have in ms project?the planned % I just follow your step but the value are not same with the % complete.

  3. Does any body knows how to fix the formula for the Milestones that is still appears #ERROR at the elapsed days column?

  4. Hi, Nik.

    Appreciate you can show in formula on how to resolve the error occur for milestone due to divide by zero.

  5. Hullo Nik, I am receiving a syntax error in step2 on the formula below – the syntax error occurs on the “d” – can you please assist:

    IIf(ProjDateDiff(Start,[Status Date])/480>=Val(ProjDurConv([Baseline Duration],pjDays)),Val(ProjDurConv([Baseline Duration],pjDays)),IIf(DateDiff(“d”,[Baseline Start],[Status Date])<=0,0,ProjDateDiff(Start,[Status Date])/480))

Leave a Reply