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.
Hi, there is an error in your formula you have calculated Planned % Complete by dividing (Elapsed date/duration),
but actually, we should divide elapsed date-/ Baseline Duration), rest of the formula is good
Hi Nik,
Thank you for all the assistance and information on your site.
Please note that I get a syntax error on the following formula in step two:
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))
Please advise how do I resolve this?
Best regards
Valentine
Hi Valentine. I had that same error and I solved it by establishing a Baseline. Hope this help you.
Hi Marcos, thanks for replying; unfortunately, I still get the same error even after base lining
change the “d” to ‘d’
I have the same problem. Even if I delete “d”, it give me another syntax error on the 0,0, part of the formula.
Can anyone please assist?
You need to retype “d” with the correct double quote symbol (“”) to replace the “d” from the copied formula.
Hi Nik, can you explain why we use 480 in the formula?
Hi Nik, i see this has already been answered
480 using for 8Hrs per day working calendar, like 8hrs * 60 min = 480mins
Hi Nik, there is an error in rows referring to milestones when duration days is 0, then the Planned Percent = Number2/Number1 gives errors because it is dividing on 0.
Anyway to solve this please.
Thank you.
How to bypass planned % vs actual %
Hi, pls can show how to calculate the numbers of days late.
Hi Nik, It’s a great formula to operate in MSP but only one issue is coming if we operate any milestones than it is showing error in the milestone task. Can you share any technique to remove this error in milestone task.
I am facing the same issue as well
I am also having the same issue.
use in colum number 3 the following formula IIf(Number2=0,0,Number2/Number1)
Please help me. Thank your support. When i add your Formular but %complete greater than %planned
same problem
Hi,
I am having this problem in Planned % Complete calculation, I have task whose Start date is 1/4/23 and end date is 2/9/23, Status date : 2/28/23, so now to calculate Planned % Complete the formula is Elapsed Days/Duration Day , but End date is already in past, elapsed days value is 39(as per status date calculation) and duration day is 27 hence i am getting Planned % Complete as 144%. Can you please help with this situation. Thanks
Hollo
After we fill Elapsed days we see of item errors, could you explain?
Can you please assist me on the following. I cannot find the problem. On my MS Projects Schedule I have the following:
% Planned and % Complete
Now here is the thing. I can only get a value % after I put in a number of days, but I do not want to do that as it is a milestone. The day must stay 0. If I make the number of days 0 then I do not get a % value, How do I change that again, please?
On the % Planned it give me a value but on % Complete it do not give me a value. As per my previous question, if you add days to the milestones then the % complete is correct but as I mentioned it is a milestone and I cannot add days.
Hi Nik, the Planned % Complete is error if I put the duration like 0,5 or 0,25 days. Can you please fix that?
using office 2021 I get error
the formula contains syntax error (using eighter “d” or ‘d’)
I changed 3rd formula based on comments to avoid milestone errors
and typed “d” to avoid 2nd formula’s error
and multiplied 3rd formula’s result by my Weight Factor column
But I still have an error in summary tasks roll up result, it’s always lower than it should be (in summary tasks)
Hi Nik, Can we show Month wise Planned % work in projects
This seems helpful, but currently all of my subtasks are ahead or on-schedule for the “Planned % Complete”. Yet my rollup to the top level task shows the % Complete as < Planned % Complete. How is that possible?
Good Afternoon Sir, I have a question, my Project start date is 31-Jan and when I put the Status date on 9 Feb, which should be 10, The Elapsed Days is 12.5, I dont understand. And when I put the Status date to today (9-Mar) I get 48.75, whereas actualy it should be 39 days. Please help & assist.