MS Project Planned Percentage Complete Vs Actual Percentage complete - Updated Improved version
ฝัง
- เผยแพร่เมื่อ 31 ต.ค. 2018
- Download the Template here - niksprojects.com/product/plan...
My Project Management Master Class: www.niksprojectsacademy.com/
Tutorial on how to create a customized field like Baseline planned percent complete in Microsoft Project. Revised version of prior video for better screen quality as per subscribers request.
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.
In this example I have used the Start date, Duration & Status date for the Planned percentage calculations.
All the formulas and details on how to create the baseline or planned % complete is available in my blog www.niksprojects.com/blog under Project Management tab.
So in MS Project duration is stored in minutes and that's
the reason why we have to copy paste the formula and convert into days so this is the formula to convert the duration which is stored natively in minutes into days so that's all what this step is. So copy from website the step two to calculate the elapsed. So insert new column call it elapsed days and copy/paste the 2nd formula. Just paste it and click OK so here I also got the error that the formula has some issue so click OK and what it does is it's not the error in the syntax but for some reason you just have to retype where the error is highlighted so click on that just retype the same thing "d" and click OK. Apply the formula for summary taks, so what it is doing is it is comparing your baseline date and calculating how many days is already over right so today's November first so it's it's stating that we have completed nine days of the project because it's November 1st and it's gonna end on 2nd so as per the plan dates we already consumed nine days so that's what this column is now we are done with if we are done with the second step right so at any point you can go back and say ok let me calculate the elapsed days as off let's say October 23rd it should be only two days right so you click on the status date and you say it's today's October 23rd you see the elapsed days is going back to two days so that's how it is calculating the elapsed tapes based on the status date so just make sure that you are setting the status they tried to have the calculations correctly now going back to website for the final step which is step number three not the final step so here we are actually calculating the planned percentage complete so again the formula is simple number two divided by number one insert a new column called number three just rename it go to custom fields rename planned percentage okay click the formula radio button click formula and say you can copy paste by the way I'm just gonna type number two divided by number one okay and then apply to summary row okay and now you have the planned percentage complete so it saying it's ninety percent complete the actual percentage may not be ninety but this is what we are calculating right if we would have followed the plan baseline dates what person date should the project be in at certain date now the next and last final step is just to format this into a percentage type of field so it has to be a text so I'm gonna click that again coming back to the web site it said text we are calling it as planned percentage complete and then copy paste the formula copy and then go here and then paste it right click custom fields formula and then paste it okay okay again it's showing me an error so you just have to delete that portion and retype the same thing there's nothing wrong with the formula for some reason it doesn't take any copy paste so just retype it and say use formula for summary rows and click OK and there you have it so once you have this you can always rename it what did we call in here as planned percentage complete just copy that and right click custom fields renamed paste okay okay so this is what we were calculating manually right and the the column that is available in Emma's project is the percentage complete so you go back ...
Related Videos
• How to get your CSM (S...
• What is Agile? Agile E...
#ProjectManagement #NiksProjects #HowToBecomeAProjectManager
What a genius you are! In a space of 15mins, you unravelled a whole semester topic just like a piece of cake. I'm impressed!!
Glad it helped, please subscribe if you haven’t already and share with friends if it helps them too
Would you be interested to learn more on project management from professional PM then checkout www.niksprojectsacademy.com
Perfect video. Been trying to write something similar myself for ages and never quite got it working, so this has really helped me out a lot!!!! THANK YOU.
Glad it helped, it’s great to hear good feedback, thanks to you for leaving a comment
This video was PERFECTION. Exactly what I needed, very clear, straight to the point, easy to follow. You got my subscription for this one! My question is how to convert the percentage in days
Thanks! glad it helped
Sir! I was doing a separate project file just to calculate this. You're help is truly appreciated.
Glad it helped
Thanks Nik. It was so helpful. please note that you can summarize all the process in just one single step: Step1: insert field text1, and copy the formula "Format(IIf(Duration>0,IIf(ProjDateDiff(Start,[Status Date])/480>=Val(ProjDurConv([Baseline Duration],pjDays)),Val(ProjDurConv([Baseline Duration],pjDays)),IIf(DateDiff("d",[Baseline Start],[Status Date])[Baseline Start],1,0)),"0%")" into the field formula placeholder. i this way the error from the milestones is also fixed.
Homa S agree, thanks. If you haven’t subscribed to my channel please do so to help grow/ interact with this PM community here.
I do realized that the Actual % Completion tends to be higher than the Planned % Completion (This formula). This is puzzling. Any idea what did I not do right?
hi Homa
I am Getting error on above formula "d" can you please provide solution
Actual is greater than Planned even after delay with this formula. Actually all the Planned % Formulas have the same issue. When the Project is Complicated and big. This formulas doesn't work. Can you help me how to fix this? Thanks
Excellent video! It was so instructive! All my questions were solved! I encourage you to show more your videos!!! It was really hard to find you!
Thanks a lot, I will try to post more often
This is exactly what I am ought to do and has been giving me a headache til today. I'm gonna try this! Thank you!!!
Thanks! Glad it helped
This was very informative! Great work! Definitely in-depth and straight forward.
Thank you!
This video was PERFECTION. Exactly what I needed, very clear, straight to the point, easy to follow. You got my subscription for this one! I am sold.
Thanks a lot Adriel and welcome to the community
Very True, Nik is great!
@@anony449 Thanks for the encouragement and kind words
Excellent video!!! Exactly what I was looking for!!!
Rafael Roberto glad it helped! Thanks for watching
Your explanation and formulas were so useful ! Thanks!
Glad it helped
thank you so much nik..i had that error and somehow couldnt figure out what is the problem..good to learn in from this video..god bless..
Glad it helped!
Love the video. Works great when I do it to individual projects but once I merge them into a Master Project folder as sub-projects, I start getting 0 values in the Durations and Elapsed columns for some of the sub-projects. Having a hard time figuring out why.
Thank you so much Sir Niks for your shared knowledge. Really appreciate and helping me lots. Again, thank you so much.
You are welcome! Glad it helped!
Thanks a lot for your help. this was the only place I could find the answer for my question.
Glad I could help!
Great video, simple and clear. Tks
Thanks 🙏
That is impressive! Thank you very much!!
Do you know if it would be possible to do that with work instead of duration? I am trying to adjust the formulas to convert the work into days and follow the same idea, but so far no success doing it for the formula in Step 2 (I tried changing "Baseline Duration" to "Baseline Work" but there is more to be done, not getting the right number yet).
Very good informative video. 15 mins well spent..
Thank you Sir
Thanks Nik! Pls share more videos
great..helped a lot. Thanks man. Keep it up
This will help to have apple to apple comparison, earlier I used to keep two mpp files one carrying fill in actual data from team members and other updated as per status date
Chetan Khanna agree, it better to have all in one place
Very useful, thanks for putting this together. As far as the error, I can't tell you exactly why, but the quotes in "d" are not being copied the font that Project expects, so it considers it a foreign language.
Thanks
Thank you Nik, amazing one!
Glad you liked it!
@@niksprojects please keep uploading videos related to TIAs.
nice, you helped me a lots. gonna show you some token appreciation. thanks
Thanks
this was very helpful. Thank you!
You are welcome
Dear Sir,
Your way of teaching is humble, and appreciated. I follow you steps by steps on the above tutorial, i face a bid hurdles during calculation of Elapsed Days, Formula is okay and applied but no counts the days in column just show zero number overall, your correction is indeed. Regards
Thanks Nick. Help me a lot.
Glad to hear.
Please consider subscribing to the channel if you haven't already for more project management videos.
Thank you, absolutely class!
Glad it was helpful!
Very good !! How can we do to have the cumulative planned percentage to draw the curve of planned cumulative vs completed cumulative ?
A great learning, thank you so much. NOW, how do I show this (% Comp vs Planned % Comp) in a S-curve?
hi nik..its really helpful..jst let me know how the same can be extracted as a report or we copy and past it in excel or word to get d same.
i need report on actual and planned progress along wth S curve as line
Thank you this videos is life saver!🤩
Glad it helped
Niks, thanks. great formula. You could make it better by validating the "milestones" using in the field "number 3" the formula IIf(Number2=0,0,Number2/Number1)
Good suggestion, have to test it out in the nested if conditions
Works well! Thank you.
You're welcome!
Thanks. It helped me.
Can you make one video,how to make visual report(chart) where we can se actual work vs. complete?
Thanks in advance.
Sure, I will try to create one
Thanks for good explaining the clear steps, there is just an error occurred at ((% complete)) it show me 0 percent for all tasks!! although changing the status date but still 0%??!! can you kind advise... Thanks in advanced... M.Hikmet (UNDP's resident Eng.)
This has worked fantastic on a new project however if you add new tasks there are errors for the planned % complete formula owing to the baseline not being set, if I edit the baseline dates as the new task date were n/a the formula doesn't work you have to reset the baseline which if your mid project effects the original baseline any ideas how to edit - this is a great tool and I applaud what you have done way past my knowledge 😃 also I think it's mentioned previously but didn't understand the reply if task duration are at 0d error in formula would really appreciate a response. Thankyou Jamie
The video really helps thank you.. only one thing that the % baseline giving error when dividing 0/0
Hi Nik, Its really helpful. I want to know that every time when i have to update the progress, will i repeat the same procedure everytime???
Hi Nick, thank you for your tutorial , but i having a problem with elapsed day is negative, how can i set all of negative days as "0"
I think the reason why there is an error after pasting the formula from the webpage is because of the different ascci or character code for the double quotes when rendered on the webpage as opposed to its ascii designation when pasted in MS Proj..just my thought..anyway, for the planned % (number 3), why not incorporate the 4th formula into it?
Thank you for the video sir.Can you make a video on how can we make S Curve out of this Planned vs Actual percentage complete?thanks
Hi Nik. Thank you for this video. Very helpful. I've followed your steps however still getting Errors on Elapsed Days for some tasks. Any suggestions on what I might be doing wrong / what I need to check further?
I am also getting those errors for some task under elapsed. Hope Nik can help. Applied to an existing project with baseline.
great video 👍 i want to calculate progress based on weightage instead of duration… can you guide on this ?
hey Niks you video is helpful but i have a question. i get -%(negative values) what might be the problem? and also what happens if you re baseline your schedule.
thank you so much for the video.
You are welcome!
Hi Nick really useful..>Why am I getting the following result of 833% 'planned' though when my activity is better than baseline?
Sir, thank you very much for a well facilitated and designed tutorial. I am still experiencing issues with Step 2 formula. After correcting the "d", there is another syntax issues in the last segment:
check out the latest template for download with updated formula - niksprojects.com/product/planned-complete-formula-ms-project-file-template/
Great Tutorial!! - I have a question - should or rather, can I do this on a Master Schedule with the Sub Projects already pulled in, or would it be better to complete this on each individual Sub Project Schedule first and then pull those into it in the Master Schedule? Hoping to get your guidance on this!!!
You can do it either way both approach should work
@@niksprojects thanks!!
@@niksprojects I have another question please: What is the reason for the Value of 480 in the Elapse days formula?
Hi, can you confirm please?
Sasha Ramoutar 480 is converting days in to minutes as that’s how the MS project store info (8 hrs multiplied by 60 = 480)
Hi Nik, 1 question. How to track & view the scheduled/planned budget by month on Microsoft Project?
Hello! Great video, very clear and informative. I am curious if you know of a way to use a date set a number of days in the future for this field? What I am trying to do is create a field that gives a planned % complete 30d ahead of the project status date (and also 60, 90). This is a report being requested by leadership of my institution, and we have many many projects to report on. Thank you for your help!
I believe you can do that by setting the status date. You can project information and set the status date to 30D, 60d ahead and formula should readjust
@@niksprojects Yes, using the status date is how we've been reporting on expectations for completion. We use Power BI to generate reports and can reference % complete in MS Project Server... But my hope is to have multiple data fields present to have those values auto-generated as dates change. I alone have 50+ ongoing projects, and individually opening each one to get predictive data is very tedious.
Hi Niks, Based on your Plan % completed VS Actual % completed, how to show in MsProject equivalent Ahead or delays in Percent(%) and in Days? What is the formula. Thanks.
Hi there! Thanks for the tutorial - I'm finding that % Complete and Planned % complete aren't rolling up to the same percentages even if the sub-tasks have the same % complete. Hope you can help!
the planned % complete is based on the project's baseline dates vs teh % completed is what you would type in manually, so it won't be exactly the same.
@@niksprojects I am facing the same problem Nik. I agree we type the % Compl manually, but we get some values if we change the status date and then click on Update Project as on mentioned status. It automatically gives us the % complete values which were planned. The individual tasks' percentage completion calculated by this method is same as your Planned % Complete method, however, the summary tasks % complete and Planned % Complete are not matching, as stated by Niki Non. Kindly look into this issue.
Hello sir, Could you share us how to calculate ahead or delay in percentage and in days?
Great video...Your video is very practical which is useful for practical applications...I am wondering that how you made the formulas..From India
Thanks Sujith. It was lot of research, trial and error , iterations to arrive at the current formula. There is still room for improvement to make it work for the milestones and summary tasks as well. Not getting enough time to refine further, but will get there soon
Hi Niks, Getting Planned % complete with the formula is explained very well. This is showing -ve % when we try to change the Actual start and end date. For example if my planned date is 1st may and planned end date is 2nd May with 1 day as duration, and if whatever reason the activity did not start and my actual start date is 4th may and Actual end date is 5th may, the subsequent activities planned % of completion is going for a toss with -ve number (for ex:-80% ). Can you please clarify why is it happening?
Hi, thank you so much for sharing this helpful process for the planned percentage. I have done everything correctly and it works fine. However, I'm currently having trouble with my planned vs actual. My project plan is designed based on scope of work. The actual progress is supposed to be lower as we are currently having delays. But it seems like it is higher than the planned (Variance +5%). Appreciate if you could assist me on this.
Hmm, it may be due to the baseline date and status date. Can you check your projects baseline dates and status date . May be rebaseline with new dates and see if that resolve the issue.
@@niksprojects Apparently, i noticed that there are errors on my elapsed days. those are the culprits. but may i know how to resolve the errors on my elapsed days? Thanks.
@@hanifhalim2388 if the errors are highlighted on the "d" letter in the formula then delete and re-type that letter and it will resolve the error on the formula.
@@niksprojects Hi Nik, im re-baselining my timeline and now the same errors happened. I tried to retype the "d" again but it doesn't work. Hope you can help me as its quite urgent. thanks a lot.
Thanks nk for understanding msp
Hello Nik, how do you add the text field to the report. I am want to my dashboard to show planned % complete v actual % complete
Hi How can we make a weightages for measure the physical progress correctly (Considering work progress and Duration)
Also check out my Project Management Master Class for a 25% discount for TH-cam subscribers www.niksprojectsacademy.com
Thanks for the tutorial - I am not finding any percentage in % Complete column, all rows show 0. Hope you can help!
Hi Niks,
Thanks for a great video, It is very helpfull.
I just wonder that for example: Actual % complete is 70% and Planned % complete is 60% then we assess the schedule is ahead?
If there are some critical tasks in this case were delayed? and free slack and total slack will not impact on total schedule of project.
Could you please advise in this case?
Thanks a lot.
Hi @Pham Nam, you are welcome! The actual % is something that we type in manually based on the progress, so if the team is stating that they have completed 70% then yes that particular task is ahead of schedule as the planned % calculate shows that it is only 60%. If you want to see if any critical tasks are delayed then you can filter in the gnatt to show only critical tasks and then look at the planned and actual % complete. Total slack show that how many days you have in slack / as buffer if delayed will not delay the project itself.
hello nick, can u show how to make recovery plan using baseline data MS project. tq
hello, your videos is ready good, i need to create an s-curve. can you show how to create an S-curve based on % complete
Good afternoon Sir,
As i am referring your tutorial for MSP planned % but whenever i am entering in elapsed time it comes 0 throughout, what should i do??
Hi Nik, thanks for your videos, have been very helpful.
I wanted to know if you can help me with an error, in my case I have tasks that its duration is on days and hours.
Do you have a formula that includes these two cases?
Thank you!
check out the latest template for download with updated formula - niksprojects.com/product/planned-complete-formula-ms-project-file-template/
@@niksprojects Thanks!!
Nice vedio and help me a lot. but the tutorial vedio is for % complete type how about if use the physical % type?
Hi Nik. Firstly, thank you for a very informative video. I see many people have had the same problem I am having - the 0,0, section of the 2nd formula keeps saying error - despite retyping it and then pressing "okay"... Please could you assist with what could be causing this error and how to correct this... There have been no responses to the others who have raised this same query??
I have updated the formula and is working on recording the tutorial, will try to share it in a few days. It’s working fine for me including for the milestone tasks. I think this time I will upload the Project file on to my website itself to download for a small price $4.99 if for some reason the tutorial and instructions sharing via the video is not working out, this way the users can download a local copy which already has the working formula
Hi Mr, Excellent video, works!
I have a doubt with the formula in the step 3, why 480?
I do some test and if I change from 480 to 960 the % change if how was divided between 2, I have a projec with 524 days of duration, use the same formula with 480 or can I change this value for 524 (duration of my project) thanks
480 is to convert the days hours (8hours) in to minutes which is required for the formula. I have updated the formula completely and now it works for the summary level tasks and milestone without giving division by 0 error. It is available for download for a minimal fee $4.99 . If interested you can purchase from here niksprojects.com/product/planned-complete-formula-ms-project-file-template/
Thanks nik.
How can I make percentage plan weekly in ms project..?.I need to draw a s curve between percentage plan weekly vs percentage completed can you help me ?
Hi Nik,
Thanks for the great tips. How come my planned vs. complete comes at 152%?
Hi, my planned % is over 100% for some items, what is the logic behind this and is it normal? some even goes to the negatives in thousands i.e. -4000%
Hello, the planned % changes when I unlink tasks. What can i do? Among a set of 8 tasks, one of the task is independent of the remaining.
hey Nick, is it ok to update actual dates of start and finish dates in project tracking ? or we just need to manually update the %complete column without changing the actual start and finish dates?
hasitha sandaruwan vidanapathirana hi, the actual start and finish dates will be updated as you set predecessors. % complete column should be manually updated to reflect the actuals
This is really useful however, it doesn't work on the summaries when the information is not sequential and overlap. On summaries it just looks at number of days past from the start to the status date and divides it by overall programme length. the does not give a true overall planned % of the tasks below. MS project calculates the %complete for summaries correctly, so there must be a way to replicate this on the summary bars for planned %. Any help greatly appreciated
Thanks! Appreciate the feedback, I will take a look
@@niksprojects Any Updates on this?
Any update?
Hi,
My basic query related to Tracking.
I am tracking my project on weekly basis.
On weekly basis I enter all values and setting status date and then updating project with rescheduling incomplete tasks after status date.
My problem is, I am not getting exact working days difference between Actual Start & actual Finish dates.
Whenever I observe Actual duration Column, that column shows only duration of split activity.
Please guide how to overcome this problem
tq sir, u saved my life
Thanks
thanks dear your video solved my big problem
Thanks 🙏, glad it helped
sir please make a vadio of s curve of percentage planned and percentage completed
s curve of same video
Hi Niks, I am interested to know whether we can plot the gantt chart for Planned vs actual in the same ?
I am not sure if we can do a gnatt chart for planned vs actual because these are derived functions / customized columns and not sure if Gnatt will use customized columns.
Maybe you should try by turning on the baseline gantt chart. There you'll have shown baseline (planned) chart and actual chart filled with adequate % completion in the same chart.
Hi there,
What happens if you have multiple baselines saved? How do you pick the baseline that you want?
we can always update the formula to use baseline1, baseline2 etc. not tried out but I am sure it can be done.
Thanks its very helpful
May I ask you, how to use those custom field in Report? ... Thank you
The custom field such as Number1 and Text1 cannot shown if using "Time" for "Select Category"🙏
That was EMAZING! what will happen if we changed the baseline? Will it take the last one?
Yes it will be based on the latest baseline
Hi nick, im having a problems with elapsed days exceed with planned days. How to correct this?.
Thanks
the example I showed is for an 8hr work day for 5 days week, is your schedule same? if it is not 8hr then you have to use appropriate hours . Ex for 8 hr workday I am dividing by 480 which is 8hr converted in to minutes (8hr X 60 = 480 Minutes). So if you have 7 hr or 10 hour work day then you have to multiply 7 or 10 by 60 and use that number in the formula instead of where I was using the 480.
i have an issue with elapsed days, so tasks that come after the status days instead of showing zero go into minus (some of them, and other remain 0)
Hi Niks, thanks for your work. I am however getting an error on the milestone tasks (0 duration), i think there is need to capture this and default it to zero.
Milestones wont work as it will give error due to division by zero as the milestone has 0 days in duration. There is a way to take care of it by adding an IF statement, I will try to make a video on that.
Niks Projects hello Nick, great job, and thanks for sharing, did you progress on the milestone formula, will be much appreciated, ty In advance and stay safe, regards
for the elapsed days formula I get the same error on the "d" and then when retype it clears but then it gives me an error on the 0,0, portion on the formula not matter what I try retyping this it does not correct so it does not accept the formula? any advice?
Great Job🤩
Thank you! Cheers!
Hi Greeting from Thailand!!
I already followed your instruction. However, when I update the status date the Elapsed days still not show the number.
Do you have any recommend for this isse? Thank you in advance.
Great video, can you tell if it’s possible to include that information in the timeline?
you can right click a task and click "Add to Timeline" from the menu you get when you right click
hey Nik, please provide me with the planned progress formula
Hi Niks
Great tutorial followed all of the instruction and works perfectly however when I reduce or increase the duration of a task i get a % in a negative or exceeds 100%. How do I rectify this issue?
Please see if you have set the baseline after making the changes to duration, status date is set correct and let me know if that resolves the issue.
@@niksprojects Hi Niks this works however I don't want to continually reset my baseline as it won't give me an accuracy I'm looking for, have you created a formula which will look at the baseline duration and the status date only and any changes to the duration does affect the Duration % Completed. IE if I have a project which has 10 x tasks for a day each and after day 3 this tasks tasks 4 x days to complete. I would like to see the Duration % Completed to indicate tasks 1 - 7 should be 100% and after changing the Day 3 task duration to 4 days the % Completed 1- 3 should be 100%. Hope my request makes sense.
@@lukekerin3933 I was about to question the same thing on this video. It seems very few people have realised that this is not applicable to all cases. Good job!
Does this work if new tasks were added that were not part of the baseline ? if it doesn't how it can be done
Sir i am getting error on last column(planned%complete), retried for several times,baseline already set but didn't helped,please help
Hi i am getting an error message on Number 3 Percentage complete what am i doing wrong
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?
Adjust your calendar
Tqvm!
You are welcome
hi Nik, thats great! What about milestone tasks? Since the duration is 0 it gives an error with this formula. D you have any hack for that?
We can tackle that by adding an additional IF statement to ignore or add specific formulas for milestone tasks. Let me see if I can create a video showing that with an example
That would be of great help! Thanks
@@niksprojects have you create the video? i have the same issue
@@niksprojects any updated on this
Hi Nick, I already follow your instructions, but I found the result is different. The formula generates a percentage of more than 100% of each task, if I refer to your video supposed to be I got the average from the total percentage of all sub-tasks. Please help me for the solution, thank you.
Sorry nik,could I ask you some thing,does the formula can show cumulative percent complete, because it seems that it just divide whole project to the same measures and when I want to draw s-curve it's like a line not anything like s - curve,and even it's different from cumulative percent complete in task usage view,tnx for ur help