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.
thanks , that’s great
can we add the new added columns . to resource usage?
I never tried using these formulas for Resource usage. Now we are following agile methodologies so I am away from MS Project. I would love to see if anyone where able to utilize the formula for resources. Please share if you were able to find the solution.
May I know, 480 meant for what?
480 = 60 * 8 (60 Mins * 8Hrs per day). It is just to convert it in to minutes for calculation purpose.
Hi Sir, If i work for 12hrs a day how would i input here like 60×12 = 720 which makes huge difference to the planned %
Hi
there is an issue with the second 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))
it said that the formula is wrong and it highlight the "d"
can you help me with this issue?
thank you
Usually if you delete the d and retype in the error will go away. I am not sure why it is, but I had the same issue when I tried to copy paste the formula.
the problem as I see it is in “formatted” quotation marks (seem like this “ is a different character that this “)
Hi, in the second formula the mistake is the quotation marks “d “ only replace de quotation marks by “d” .
Hi Niks, its great working by you, however there are some mistake in same , as when we fill manually % complete, it calculates the overall percentage based on sub activity’s percentage completion. However as per your formula the planned percentage is calculated based on dates. It may be wrong as many activities are parallel and start to start, hence the percentage complete cant be based on only time/ duration of activity, it should be each activity work along with time.
Hi.
Please could you help me, I copied the formula #2 and I´ve replaced the “d” to delete the error, but I still have an #ERROR message in the colum. and I don´t know whay.
I have the same problem. Did you get any answer from Nik ?
Hi Nik, I’m still using project but would love to hear about tools you’re using for Agile PM work. Thanks for the formula’s.
Hi Nick thanks for the tutorials, i have followed each step very closely to the end but my elapsed days and planned percent columns all register 0 thereby making my planned % complete column also appear 0%. Can you help me out pls thanks
How can you account for non-working days (e.g., Holidays)? Is there a formula that can be incorporated to tap into your defined non-working days? Otherwise, the % complete roll-up is never 100% accurate.
hi Nik Im having a problem also with the elapsed days. Its appear 0%. Im following your steps-by-steps but still couldn’t get the correct elapsed days correctly. Please help
Hi, i followed your instruction but some of my elapse date and planned% showed error?
How to solve ,please help me
Hi Nik, your instruction has been helpful. However, how can i avoid the annoying #Error for division with zeros?
With regards to the issue on letter “d” just retype the whole formula and it works for me. Thanks Nick
Hello, great post, same issue as above, division by zero (Milestones), can be saved through an if formula that provides a 0 instead of an error.
It does not work!!
step 4 Format( expression[, format[, firstdayofweek[, firstweekofyear]]] ) , saying syntax error and highlighting expression
please advice
Have you try to build it in project center? If yes, i need your help.
Hello Nik,
Please post on how to plot the % complete vs % planned complete graphs.
Thanks,
Ajinkya
Hi – does this work with MS project Standard 2010 – I have set up all the fields and formula as per your instructions – so i am getting values in the planned % complete feild which is good. However on inspection the calculations are incorrect particularly in the summary roll up bars and it appears the elapsed days calc is where the errors are.
I have used the following formula to calculate elapsed days : 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 have a project with activities extending beyond the status date and on running the calculation the %complete is set to 100% !
Can you help / shed any light on this problem ?
I am having the same issue.. wondering how to allow this to work across all task levels?
Must you have a schedule baselined to calculate the Planned % Complete
HI,
Applied these formulas, however it is not functioning properly when tasks are moved/rescheduled (and baseline still kept). Showing negative values and overall result became incorrect.
How can it be fixed?
Hi Alex
The calculation is based on the baseline so if tasks date are changed then you have to rebaseline
Roll up dosent work.. ? Any comments on how to fix this ?
Hi, I have something that works but struggling to get rid of the ERROR that is appearing with milestones? Where do I add a formula to show a 0 instead of ERROR? Thanks in advance
Not zero, rewrite wherever you have ‘d’ in the formula
Sorry, that wasn’t very clear. What formula do I need to write? Thank you
On the Step 2 formula – below, after copy pasting the formula, delete the “d” and type back in again. it’s weird but somehow it works.
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))
Works when you put the formula you have in the video for Step 2.
IIf(DateDiff(“d”,Start,[Status Date])>=Val(ProjDurConv(Duration)),Val(ProjDurConv([Duration],pjDays)),IIf(DateDiff(“d”,Start,[Status Date])<=0,0,DateDiff("d",Start,[Status Date])))
The other one does not work.
Sir,
if milestone pass the status date then it should come to 1 then we can get 100% milestone complete
what will the formula update in your formula that is..
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))
The Step 2 formula is still given 0 and error for the elapsed as below, even when I set the status date to 12/03/18.
Is there any help to that?
PRE-CONTRACT STAGE 29 days Wed 10/01/18 Mon 19/02/18 0% 29 0
Price Negotiations 15 days Wed 10/01/18 Tue 30/01/18 0% 15 0
Signing of Contract 5 days Wed 31/01/18 Tue 06/02/18 2 0% 5 #ERROR
PO Processing 3 days Wed 07/02/18 Fri 09/02/18 3 0% 3 #ERROR
Deposit Receipt 6 days Mon 12/02/18 Mon 19/02/18 4 0% 6 #ERROR
12/01/18 rather and not 12/03/18
thanks
If you have a sample MPP with the error you are getting, I can take a look. Send to niksprojects@gmail.com
Sent a sample project plan with rollup error. Please respond once get chance. Thanks in advance
Nik, I am also having the “#ERROR” issue. I have deleted the “d” and typed it back in, and I’m still receiving an “#ERROR” issue in the Elapsed Days column. Can you assist?
Hi Nik,
Please note, for all the milestones activities in the plan #ERROR is appearing. Since the duration are “0” days for milestones. The duration of days and elapsed days also are “0” hence the quotient of Number 2/Number 1 is also “0”. Hence the issue. How do you think we can rectify it.
regards,
MS
Try adding an IFF(condition, true, false) statement. So if (duration=0, display 0, else rest of the formula).
hi, could you please elaborate or show the complete formula? Thank you
Did you get the complete formula for this ?
hi nik, your blogs helps me a lot. I’m try to working on % delay 7 delay in days. I have some questions, logically if I want to fine the % delay I have to just minus the % complete with the planned percentage. I’ve do that but it come out to ERROR. can u help me? thanks 🙂
**correction % delay “&” delay in days
I am not sure if I followed your question, can you explain with an example on what you are trying to achieve.
I don’t knw how to attach picture here. but I’m now working on work program for construction project. the program have everything as per your tutorial above. I have to add column % delay, and delay in days. previously I using Microsoft excel but our client request in Microsoft project. according to M.excel if I want to find the % delay, I have to just minus the value of % complete with percentage planned (and i got the % delay). to find the delays in day i hav to mutiple the % delay with task durations day. then I got the delays in day for every task. am I confuse u?
Thanks Nik. Can you explain why I am getting negative numbers for Elapsed Days? It is only happens in tasks that have not started yet, but not all of them. Most are 0 which is what I would expect for tasks that have not started yet.
Nik, I’m having problems calculating the elapsed days. For start dates that have already occurred it shows as zero and for dates in the future it shows an error. I’ve already confirmed that project start and status date are correct. Any recommendations?
Have you baselined your project?
Nik, I have followed your steps to achive the planned % completed but I’m getting negative values and it shows percentage more than hundred.
Thanx for your work!!!
set baseline and you will not get negative duration
HI Nik – I am getting #ERROR for the Elapsed Days (step 2) formula. I see other have the same issue – is there any guidance you can provide that will help me troubleshoot this?
Hi Nick, great blog and details. Quick question, if i have multiple baselines as in baseline 1, baseline 2 and baseline 3 how can we revise the planned % and Actual completion percentage based on the baselines. Thank you in advance.
Thanks Nick much
Could You help me how to caculate monthly planed % completed. Because we are tracking by week or month. Thank you.
IF you are getting an ERROR#, you may need to set a project baseline and assign a project status date in order to allow the used parameters to work. Reach me out for help
Nik there is a clarification required, if using the above formula i have planned percentage of 2%(for example from sep1 – sep 7) in the first week of the project and i put acutals to all the date (from sep1 – sep7) 100% complete for the activities haveing the mentioned dates the actual % complete should be 2 % to , but this doesnot happens. what ot do for it then please ?
Hi Umair Ali, I am not sure if I followed your question. The actuals are the actuals you type in to MS Project, the baseline % complete calculation is used to see how much ahead or behind are you with the planned (baselined) %
Does this work on MS Project 2010? I’m getting #ERROR in the output of Elapsed Days. When I checked in the custom field again, the [STATUS DATE] changed to [Text1] as below:
IIf(ProjDateDiff(Start,[Text1])/480>=Val(ProjDurConv([Baseline Duration],pjDays)),Val(ProjDurConv([Baseline Duration],pjDays)),IIf(DateDiff(“d”,[Baseline Start],[Text1])<=0,0,ProjDateDiff(Start,[Text1])/480)).
Please help. Thanks.
It should work in all MS project version, just make sure you have baselined your project and a status date is selected in the project information window
I updated the video, watch follow along and see what you are missing
Thanks for sharing your expertise Nik. Regarding the % Planned Complete, the values I’m getting doesn’t adjust when I change the Status date to a future date (ie: Dec 2019 – expected project completion date). However, if I adjust the Status date to July 2018, the % Planned Complete adjust accordingly. Is there a simple solution to this?
Did you extend the project from a earlier finish date to a later (Dec 19)? If so then you would have to rebaseline the project. The formula uses the baseline dates to compare against the actuals. So if you have moved the finish date to Dec, try rebaselining the project and then see if the calculation is working.
it’s not working in my new project. but in my old project it’s still working. i did as u said but not working now. i selected new status date but planned % complete column still showing zero %. why?
Did you baseline the project?
I did not add baseline.. is it necessary
Baseline dates are used in the formula so baselining is required.
If duration is kept “0” showing error in planned percentage
Yes that is due to division by 0 error as the formula is designed for tasks and not milestones (0 duration)
Also in some column its showing percentage completion more than 100%
Hi Nik….need a customized column to show “Schedule variance” which would be difference between planned & % complete filed…can you please support?
Thanks,
Nithin
Hi Nithin, For schedule Variance, you should be able to compare the new variance to the baseline. If the project is baselined, then let’s say you updated 1 task to increase the duration from 1 to 2 days. You can see the variance by clicking the Gnatt view and then on the View tab, click Gantt Chart. In the Data group, click the arrow on Tables, and then click Variance. Compare the values in the variance (start & finish) columns against the Baseline start and finish.Let me know if you would want me to create a video on the same.
Hi Nik, This is a great tutorial. Question though, my project plans use a lot of milestone dates. So when the milestones are complete, project gives a 100% which is averaged through the actual % column. The formulas above, generate a 0 for the milestones, even when complete, so my average of planned % is significantly lower. 45% to 4%… How can I adjust this?
Hi sorry about the delay, to handle milestones we may have to add more iff conditions to exclude milestones from any calculation
Hi Dave, Thanks for the comments. Can you confirm that all the milestones are set as 0 days, I understand the formula doesn’t take care of milestones but I am not sure why would the average go from 45% to 4%, it should be off by only 1% (44%) as all milestones are 0 days. I will check it out anyway, do you have any sample file that you would like to share that I can take a look. Also if you are interested in MS Project / Project management related videos, please check out my youtube channel and subscribe. (Sub link : https://www.youtube.com/c/niksprojects?sub_confirmation=1)
Hi Niki,
‘ % complete’ field in MS plan does the same thing as your derived column. What is the difference between % complete column and the one you derived. Please let me know. I am looking for a % complete derived based on baseline duration
Hi Arun,
The % complete field in MS Project is for the user to key in the actual % complete, the new custom column that I derived is to calculate the planned % complete as of certain date based on the baseline.