MS Project Planned Percentage Complete Vs Actual Percentage complete - Updated Improved version
HTML-код
- Опубликовано: 21 авг 2024
- Download the Template here - niksprojects.c...
My Project Management Master Class: www.niksprojec...
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
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
Also check out my Project Management Master Class for a 25% discount for RUclips subscribers www.niksprojectsacademy.com
Sir! I was doing a separate project file just to calculate this. You're help is truly appreciated.
Glad it helped
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
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 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
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
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
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!
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%
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
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 good informative video. 15 mins well spent..
Thank you Sir
Your explanation and formulas were so useful ! Thanks!
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!
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
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!
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
Hi Nik, I managed to get the planned %, but the % in % complete is not tally to planned % after I key in the same % in % complete. Would appreciate it if you can explain why. Thanks
Hi Bryan, I have the same issue with you. Have you resolved this? Can you share with me if you found the solution?
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???
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
The video really helps thank you.. only one thing that the % baseline giving error when dividing 0/0
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
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
This was very informative! Great work! Definitely in-depth and straight forward.
Thank you!
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?
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
The reason why you need to "re-type" the ("d") is because the quotation marks used on your website (“d”) are not the standard quotation marks ("d")
Ok thanks
Sir i am getting error on last column(planned%complete), retried for several times,baseline already set but didn't helped,please help
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
Excellent video!!! Exactly what I was looking for!!!
Rafael Roberto glad it helped! Thanks for watching
This formula only tells you what percent the status date is of the entire project duration which is not the same as percent complete. It is just % time elapsed of the project total. What is needed is a percent complete approach that mirrors the MS Project % Complete column which totals the % Complete of all the activities weighted by each of their durations.
Very good !! How can we do to have the cumulative planned percentage to draw the curve of planned cumulative vs completed cumulative ?
Great video, simple and clear. Tks
Thanks 🙏
tq sir, u saved my life
Thanks
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.
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
A great learning, thank you so much. NOW, how do I show this (% Comp vs Planned % Comp) in a S-curve?
Nice vedio and help me a lot. but the tutorial vedio is for % complete type how about if use the physical % type?
nice, you helped me a lots. gonna show you some token appreciation. thanks
Thanks
great video 👍 i want to calculate progress based on weightage instead of duration… can you guide on this ?
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
Sir, this calculation is based on days not manhours. If we want to do calculcations based upon elapsed manhours, what will be the formulae???
I haven’t thought about it in detail, but just thinking out loud , isn’t it possible to convert man hours to days, meaning If the task takes 24 man hours then it is equivalent to 3 days (24 div by 8). Can you use that way or is that too much of a hassle?
@@niksprojects I think, it is not too simple, because, this formula will not work in that case. Need to create another formula.....
Hello sir, Could you share us how to calculate ahead or delay in percentage and in days?
Thank you, absolutely class!
Glad it was helpful!
Hi Niks, why does my elapsed days still showed 0, i had complied all the steps. after updating the status date it still showed 0. please assist tq
You can download the updated template with new formula here - niksprojects.com/product/planned-complete-formula-ms-project-file-template/
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"🙏
Hello Sir, I am facing the issue till the time that planned % is not reflecting in summary tasks.
Can you please let me know that why it is happening ???
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)
Thank you Nik, amazing one!
Glad you liked it!
@@niksprojects please keep uploading videos related to TIAs.
Hi, how do you eliminate calculating the milestone in the programme. It showing error on the table
You can do that by IIF condition, if milestone then display 0 before applying the rest of the calculations.
this was very helpful. Thank you!
You are welcome
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.
Hello thank you sharing such a grate and wonderful video which is very much helpful for me but the Elapsed formula is not working for me can you please share the formula afn copy it i have typed everything but it was not working for you please help me with this situation.
A question, your formula use duration as reference but if the task has different time for actual work for exp,a two days duration task, first day of doing the task is current day and the other happen 10 days later the formula at the second day says the task should be 100% but the the task will complete at the 10th day,what should I do about it?
Could you please show me what the formula should be in step 2 if the duration is in hours. Still cant get the elapsed hours into hours which still seems to be showing in days.
I convert in to days by multiplying 480 = 8 hours in a day X 60 minutes. So if you don’t want to convert in to days then try multiplying by just 60 to convert minutes in to hours in that step
@@niksprojects Thanks.. finally got it
@@swiftandsimple1983 great!
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).
Hi,Ideally when & how many Baselines to be created. And secondly how do i get duration variance wrt to current (latest baseline).When i check duration variance its compares with 1st basline.
Just need 1 baseline to be created. Also make sure in the Project tab under Project Information the status date is set to current date. In Step 2 we use the Baseline in the formula as you can see here 6:39 so make sure that you use the correct baseline in the formula if you have multiple baselines or clear the baseline for the entire project (if it's ok) and the set the baseline again.
@@niksprojects Thank you very much for your instant reply.
If I want duration variance wrt to latest baseline then what should i do?
Please guide
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!
Can we use these formulas even if the tasks are setted manually without predecessors?
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
Thank you this videos is life saver!🤩
Glad it helped
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.
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
I still have error for the elapse days, the problem is not the formula, it's just the elapsed days still appear zero even some of the task are still in error eventho I aldy select the status date
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"
not working for milestone. it show error and not even show in summary the %
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?
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.
Hi Nik, 1 question. How to track & view the scheduled/planned budget by month on Microsoft Project?
Hi How can we make a weightages for measure the physical progress correctly (Considering work progress and Duration)
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/
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.
Thanks Nik! Pls share more videos
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
Pls show me detailly fomular at the step 2. I can not change the fomular on ''elabsed days". Maybe fomular is mistake. Thanks so much
many error then how to solve (elapsed days) i follow step by step ..same error
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.
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 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.
Thanks for the video. It happens that Actual% is greater than Planned even after delay with this formula. Actually all the Planned % Formulas have the same issue. even when the delay is very visible . This formulas isnt working or something else. Can you help me how to fix this? Thanks in advance
So the MS Planned % Complete does not follow the accurate EVM formula? espacially at WBS/Summary Level
Ho do you find difference of planned and actual progress percentages??
You should be able to add a custom number column and then use a formula to do the calculation (substract) function
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.)
Hi Nik,
Thanks for the great tips. How come my planned vs. complete comes at 152%?
Thanks nk for understanding msp
Hi Nick really useful..>Why am I getting the following result of 833% 'planned' though when my activity is better than baseline?
Hello. formula does not work - VaI(ProjDurConv(Duration, pjdays))
what about milestones ? 0/0 throws an Error
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.
Thanks for the tutorial - I am not finding any percentage in % Complete column, all rows show 0. Hope you can help!
Hello When I update project information date some percentage are in -150%.. why is that?
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.
Hi , please help . I am struggling with the second formula and cannot seem to be able to fix the error
how to compare planned percent complete and % of complete-- Means 50% is the planned percent and 40% is the actual % complete-- Then 40/50 % = 80% is the Adherence... How to calculate this
Hi Sir, I used your formulas on my project but im having issues that my %complete is not updating
This tutorial was very helpful. How can we show this phenomena in the form of graph?
Thanks! Easier option would be to copy both % columns and activities to excel and create a graph in excel
Hi Nick, am getting an error on step 2 formular. It highlights ProjDateDiff. I tried retyping but I get the same error.
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?