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 01**: **Calculate 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.

__Formula__: **Val(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 02**: **Calculate 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.

__Formula__: **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))**

__Syntax__: **IIf( expr, truepart, falsepart )**

__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 03**: **Calculate 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.

__Formula__: **Number2/Number1**

__Syntax__: **Division (Math)**

__Formula Explanation__: Dividing the Elapsed days by Duration of the task to get the percentage of completion for the task.

**STEP 04**: **Format 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%”)

__Syntax__: **Format( 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.

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

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.

I got the same problem, even the project is on schedule, there exists a difference between planned % and actual %. Did you know the solution? thanks in advance.

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

Did you ever get an answer to this?

I used this.

IIf(Number2=0,0,Number2/Number1)

Seems to work for me.

Hi, Nik.

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

did get a solution to this your comment?

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))

I keep getting the same syntax error as well.

Try retyping in the “d” wherever it is in the formula

I tried retyping “d” and still got the error

I THINK THE DEFAULT OF MS PROJECT ALREADY COMES WITH THE ACTUAL & PLANNED.. AND ALSO INCLUDE THE FINISH VARIANS TO SHOW HOW MANY DAY ARE DELAY BASED ON CURRENT STATUS DATE. NO NEED SUPER FORMULA TO SHOW THAT

Hi Nik,

Thank you for the sharing.

The way i normally generate my % schedule is by creating a copy of baseline file, then use the “update project function & status date” to generate % complete as if the activities are runs as per planned.

I just wondering, why did we use formula to generate the % schedule

when we can simply copy the value generated in % complete in the baseline.

Appreciate your feedback.

Wan

Thanks for the post but It does not work (Step 2 #Error)

Try retyping in the “d” wherever it is in the formula

Hi Nik, I just tried your formula but it is showing me lot of >100% and <0% values against activities. How can I fix that? Also, how did you arrive at 480 as denominator in 2nd formula.

8 hours/day x 60min/hour = 480min/day

I think it is the minutes in a day, which is 8hrs * 60 minutes= 480.

If our working hours are different, I think we should change it accordingly.

Its just a guess, and when I corrected it , it showed me right value.

Hi Nick, wondering if you can help please. all I get is error codes in each column. can you please advise

why is my Elapsed Days always 0? i put the status date to today already

Hi all,

Does this elapsed day formula include weekend/public holiday, because one of my task require work on weekend/public holiday, but it seems the elapsed formula not working well on this condition…the date for that task have over but the elapsed day still showing 0. please help 🙁

Hi nick, im having a problems with elapsed days exceed with planned days. How to correct this?.

Thanks

How can we managed a task that has elapsed more days than planned. I have a task that was originally 11 days duration. Currently there are 15 days elapsed but task is only 33% complete (Actual). The formula shows that the planned % complete should be 136%. Is there a way to change formula to put in 100% if output is greater than 100%? Thank you

I get ERROR for milestones, can you tell me if you know how to correct that?

I have used the formula for planned % complete , but I am getting error

Does the planned percent complete always show way less than the % complete if you are 100% completed with the tasks shown by date? Basically we are exactly where we are supposed to be in the project, but show 83% completed to a plan of 56%…? Makes no sense. Can you please help?

hi i want to make this on actual start- actual finish how can i do that?

I was able to refine Step 3 formula to avoid:

1. Negative numbers

2. #Division by Zero error

3. Setting Min = 0% and Max = 100%

IIf((Abs(IIf([Number1]=0,0,([Number2]/[Number1]))))>=100,100,IIf((Abs(IIf([Number1]=0,0,([Number2]/[Number1]))))<=0,0,(Abs(IIf([Number1]=0,0,([Number2]/[Number1]))))))

Enjoy!!!😎😎

Changing to 1 instead of 100:

IIf((Abs(IIf([Number4]=0,0,([Number5]/[Number4]))))>=1,1,IIf((Abs(IIf([Number4]=0,0,([Number5]/[Number4]))))<=0,0,(Abs(IIf([Number4]=0,0,([Number5]/[Number4]))))))

HI the percentage of complete its appearing as 5688%. I just want to know how to fix it