# MS Project Actual vs Planned % Complete

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.

Where did you put this ? I see no change

IIf(Number1=0 And Number2=0 And [Status Date]>[Start],1,IIf([Status Date]<[Start],0,Number2/Number1))

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?

I have added the formula below to Number3 column to resolve the milestone issue:

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

The idea here is that if the duration is zero (it is a milestone task), check elapsed days. If elapsed days are greater than 0, then planned percentage for this task is set to 100%. If elapsed days is zero, then planned percentage is set to zero. A milestone task can only be 0% planned or 100% planned. If duration is more than 0, then I follow the original formula.

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

just delete “d” and retype it.. Worked for me

I face the same problem also, especially for those tasks with predecessor. Please advice

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

hello Grace did anyone answer this issue?

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

Did you figure out how to fix the issue with getting greater than 100% planned % completes? I have -600% on some tasks and 300% on others.

**I found the issue with the greater than 100% planned percent completes and negative planned percent completes for anyone – Set the baseline and if you already had one set, overwrite the current one and it should fix it!

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

Did you figure out how to fix the issue with getting greater than 100% planned % completes? I have -600% on some tasks and 300% on others.

**I found the issue with the greater than 100% planned percent completes and negative planned percent completes for anyone – Set the baseline and if you already had one set, overwrite the current one and it should fix it!

how can i on the formula window click the option “Roll up to summary level” to get the correct percentages for the summary tasks? i cant find Roll up to summary level. please help me

is there anyone has the solution for this?

Hey i wanted only the planned % so made some small qweaks

The First formula

Original – Val(ProjDurConv(Duration, pjdays))

My tweak – Val(ProjDurConv([Baseline Duration],pjDays))

As i wanted as per the planned duration.

The Second one

Original – IIf(ProjDateDiff(Start,[Status Date])/480>=Val(ProjDurConv([Baseline Duration],pjDays)),Val(ProjDurConv([Baseline Duration],pjDays)),IIf(DateDiff(“d”,[Baseline Start],[Status Date])=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))

Now this won't impact the % planned

Hi Nik, thank you for the formulas, I have been looking for that. I would now like to add the planned % complete to the Dashboard graph. Though in the field list, the – number – custom field box it is referring now to “number4” not “text1”, as I set it in the custom fields. Therefore the Dashboard graph is showing “0” for my planned % complete. The numbers are all correct in the Gantt chart. Is there a way to fix that for the Dashboard?

Thank you in advance, Nathalie

Hi Nik,

I am getting an #error msg in the Planned Percent Complete Column. I have Checked my to see if the formula of (Number3, “0%”) is correct and it is and was accepted. I also checked the status date and its correct.

Please assist.

Thank you,

Regards

BW

Nik,

I found my error. I omitted Format in the formula. It works perfectly

Thank you,

Regards

BW

Hi can u share what error that you omitted? I faced the same issue

Hi, I’m facing the same issue

Please tell me if you knew how to fix it

how about for the split task, I’m having a difficulties for elapsed days if I split the task.

in one summary task, i am getting more than 100% as planned percentage. Kindly help.As it is very urgent for me.

Did you figure out how to fix the issue with getting greater than 100% planned % completes?

**I found the issue with the greater than 100% planned percent completes and negative planned percent completes for anyone – Set the baseline and if you already had one set, overwrite the current one and it should fix it!

Hi Nik. Is there a way to convert the finish variance in the same manner as you did for total float?

Hi Nik, great tutorial!

how do I graph the Planned Percent in an S Curve?

Hi Nick

I get an error on the second formula. on this part )<=0,0,ProjDateDiff([Baseline Start],[Status Date])/480)), The 0,0, doesnt want to stick keep on getting error on this portion even I retype allot of times.

I also encountered the same error, the 0,0, remains error.

Hi Nik… Thanks for this. I am trying to get the planned % complete which this does for most except where elapsed days = 0…. how do I get around this? Many thanks

Hello,

I tried to implement the suggested % Planned formula in this post, but I saw that the formula is not accurate in the summary tasks in my real world scenarios, specially where in situation where many tasks are grouped in a summary task and there is a unused date interval between the end of a task and the start of the next task.

After a research with some colleagues, I’ve managed to get the formula which seems to be working right and correctly disregard the “dead” interval on the summary tasks.

See below the 5 custom fields that need to be created in Project. Please consider that the formulas 1 to 4 are only support for the formula 5 which is the one that should be displayed in the columns list:

1. Custom Field [Duration3]

Label = “Total Planned Duration Rev.”

Calculation for summary rows = Rollup (Sum)

Formula = IIf([Duration]=0;1;([Duration]/(ProjDateDiff([Start];[Finish])/[Minutes Per Day]))*(ProjDateDiff([Start];[Finish])/[Minutes Per Day]))

2. Custom Field [Duration2]

Label = “Total Planned Duration”

Calculation for summary rows = Rollup (Sum)

Formula = IIf([Duration]=0;1;ProjDurConv(ProjDateDiff([Start];[Finish])))

3. Custom Field [Duration1]

Label = “Planned Duration up to Status Date”

Calculation for summary rows = Rollup (Sum)

Formula = IIf([Duration]=0;0;IIf([Status Date]>[Finish];[Duration];IIf([Status Date]>[Start];([Duration3]/[Duration2])*(ProjDateDiff([Start];[Status Date]));0)))

4. Custom Field [Number1]

Label = “# Plan”

Calculation for summary rows = Formula

Formula = IIf([% Complete]=100 And [Duration]=0;100;IIf([Duration]=0 And [Status Date]>=[Start];100;IIf([Duration]=0 And [Status Date]<[Start];0;IIf([Duration3]-[Duration1]<=50;100;cint([Duration1]/[Duration3]*100)))))

5. Custom Field [Text1]

Label = "% Plan"

Calculation for summary rows = Formula

Formula = [Number1] & '%'

In the end, after some studying on these formulas, my understanding that the key point is that they actually do a SUM of the efforts in days of all individual tasks and uses this sum to perform the calculation, disregading "dead" intervals that shouldn't be taken into consideration to calculate the % Planned progress.

Hope this may help some of you.

-Felipe

Thank you Felipe, it works perfectly for me

why must divide by 480 for the elapsed days formula? what is the 480 means?

randi chor

The formula for the elapsed date is not working in MS projects 2010

Hi Nik,

I tried to calculate the Elapsed Days with your formula and tried to set the future days (not started) to ZERO, but it is still giving me the Negative Days and Negative %. How to fix it

Awasome. Thanks. This works.