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.
I am also having the same problem. If i enter the Actual %age of the project to match the planned % i.e. all activities in the project are on time, there is a difference between the %planned and %actual. They are never equal to each other.
Actually , I am with you and I do not understand why we have to calculate so called “Planned%” when % complete reflect “planned%” at the cut off date. I would be happy , if Mr.Nick could explain. Thx
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 Nick, Was there ever a solution for the 0,0, error on the second formular?
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
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
Thank you Felipe, it works perfectly for me
Awasome. Thanks. This works.
Is it possible to add the cistom fields in burndown chart in MS Project
After the said Plan and actual % data is available, can we generate a bar chat showcasing the Plan vs Actual data?
Hi. How to calculate Percent Plan for Late Start/Finish? Thanks
We are getting negative values in the Elapsed Days. Could you please let us know what could be the error
Hi, can use save this as a template or can you only add these columns after you have set a baseline? Do you need to do all tehhese steps for every new program?
Hi, your formulas are working fine and thank you. when I made a subproject with these formulas it is not working in the sub project. but it is working good in individual project. can you help to solve this please!
Hi, this is very helpful. One question, why does it not record values for Summary Tasks? Actually when tracking the Planned %, measuring it against the summary is key.
Hi, I have had success with this formula but not for milestones, has the formula been updated to also resolve the milestones error? Thanks!
Why planned % can be in negative result and 200% instead of 100%?
I also see the elapse can be in negative number as well.
What should i do?
I am getting “error” references in some of the columns associated with “planned Percent and Planned % Complete”. Can you please explain why this might be the case?
Hi Thank you for your sharing,
I had did same like this but unfortunately some of the tasks shown elapse day in negative value and the Planned % Complete in zero Value