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.
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?
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
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.
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
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.
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
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)
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
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
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
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.
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 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?
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?
@@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!
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 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!
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 Nik, can we replace assigned over allocated resources with additional resources when task is in progress? how and do we see work for them after assign?
Hi Amit, under the resource menu on top ribbon , you will see resource sheet button. Click on it to see all assigned resources, update the new resource here and then go back to task and assign the new resource for the task
@@niksprojects Thank you I was done same for assigned new resources, and reload to the task, but when I removed existing resources from in progress(active) task and assigned new replaced resources to the same task work shown 0h, my concern is need to add more same resources with in replacement of over allocated resources. possible for new assigned resources show work in resource usage sheet.
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,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.
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.
@@niksprojects : Please guide because I have problem after adding formula, my all elapsed days became Zero (0). I have set base line and 34 days passed in my Project activity. It should show 34 days but it shows all zero in elapsed days column.
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
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, 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.
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
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/
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 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.
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!!!
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:
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.
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
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
Hello Nik, I tried your Planned % Completion formulae, but when we start tracking the progress of the project, 'Duration in days' is changing with respect to 'Actual Duration' in Tracking Table, but not the Baseline Duration. As a result my 'Planned % Complete' comes out to be wrong. Please tell me what change is to be done in the formulae.
Hi Pranav - In Step 2 we are using the baseline duration and comparing against the project status date. See the formula here Formula: IIf(ProjDateDiff(Start,[Status Date])/480>=Val(ProjDurConv([Baseline Duration],pjDays)),Val(ProjDurConv([Baseline Duration],pjDays)),IIf(DateDiff(“d”,[Baseline Start],[Status Date])
@@niksprojects Yes Nik, I verified the formula, it states Baseline Start and Duration. But there is some issue with Baseline Finish or Actual Finish. When I put the Act Finish = BL Finish, the Planned % Compl comes out to be 100%. However, when I put Act Finish < BL Finish, P%C comes out to be more than 100%. Also, vice versa happens with Act Finish > BL Finish. Could you kindly check this issue in the Tracking Table, and get back to us. Thankyou.
Hi thanks a lot for the tutorial! It works! But, i have an error for milestone task that has 0 days. My Planned % Complete showing error and understandably so because any number cannot divide by 0. So what should i do to tweak the formula? Thankssss!
It’s a complex formula to take care of the milestone issue and is error prone, so I tweaked the logic completely and using duration- check out the link here niksprojects.com/product/planned-complete-formula-ms-project-file-template/
Nik May thanks for uploading this video, I'm facing an Issue while monitoring the schedule. The milestone activities are not getting updated the % planned progress. How to resolve it, if you are having an solution for it pleas do let me know. Many thanks in advance.
I had to revamp the full formula and I have posted on my website if you would like to download for a small fees. 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 Nick,I hope you are fine...I have problem,that when I insert column of duration in days after assigning baseline,my duration does not show in WBS,where may be sth wrong in my working please.
Thanks to you for present an excellent video. My problem is when I change duration as per my field condition that time Planned % complete value (-)% come. It is ok??
Hi Niks, thanks a lot for this, but if I change status date sometimes it gives me negative % complete as elapsed days are negative. What can I do to solve this. Please respond if you have time
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?
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??
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?
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
Also check out my Project Management Master Class for a 25% discount for TH-cam subscribers 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
Sir! I was doing a separate project file just to calculate this. You're help is truly appreciated.
Glad it helped
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
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
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
Thanks a lot for your help. this was the only place I could find the answer for my question.
Glad I could help!
Your explanation and formulas were so useful ! Thanks!
Glad it helped
Very good informative video. 15 mins well spent..
Thank you Sir
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!
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
This was very informative! Great work! Definitely in-depth and straight forward.
Thank you!
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!
Thank you Nik, amazing one!
Glad you liked it!
@@niksprojects please keep uploading videos related to TIAs.
Great video, simple and clear. Tks
Thanks 🙏
nice, you helped me a lots. gonna show you some token appreciation. thanks
Thanks
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 this videos is life saver!🤩
Glad it helped
Thank you, absolutely class!
Glad it was helpful!
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
this was very helpful. Thank you!
You are welcome
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
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.
Very good !! How can we do to have the cumulative planned percentage to draw the curve of planned cumulative vs completed cumulative ?
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
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
A great learning, thank you so much. NOW, how do I show this (% Comp vs Planned % Comp) in a S-curve?
tq sir, u saved my life
Thanks
Works well! Thank you.
You're welcome!
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
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?
great..helped a lot. Thanks man. Keep it up
The video really helps thank you.. only one thing that the % baseline giving error when dividing 0/0
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 Nik! Pls share more videos
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?
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
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.
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!
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%
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. 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
thank you so much for the video.
You are welcome!
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 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"
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!!
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"🙏
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
Great Job🤩
Thank you! Cheers!
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 Nik, can we replace assigned over allocated resources with additional resources when task is in progress? how and do we see work for them after assign?
Hi Amit, under the resource menu on top ribbon , you will see resource sheet button. Click on it to see all assigned resources, update the new resource here and then go back to task and assign the new resource for the task
@@niksprojects Thank you I was done same for assigned new resources, and reload to the task, but when I removed existing resources from in progress(active) task and assigned new replaced resources to the same task work shown 0h, my concern is need to add more same resources with in replacement of over allocated resources. possible for new assigned resources show work in resource usage sheet.
great video 👍 i want to calculate progress based on weightage instead of duration… can you guide on this ?
Hello sir, Could you share us how to calculate ahead or delay in percentage and in days?
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 Nick really useful..>Why am I getting the following result of 833% 'planned' though when my activity is better than baseline?
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,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 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 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.
Good work, keep it up.
Thanks Naveed
@@niksprojects : Please guide because I have problem after adding formula, my all elapsed days became Zero (0). I have set base line and 34 days passed in my Project activity. It should show 34 days but it shows all zero in elapsed days column.
Under project tab -> project information , all dates are set correct. Especially current date and status date
@@niksprojects : Yes, all ok. Can I send file through email, if u dont mind. Please help and fix problem.
Thanks
Sure I can take a look
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
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, 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.
Can you tell me how can we make s-curve or bar chart data from these number roaws?
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
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/
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 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.
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)
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/
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.
Nice vedio and help me a lot. but the tutorial vedio is for % complete type how about if use the physical % type?
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!
Hi How can we make a weightages for measure the physical progress correctly (Considering work progress and Duration)
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
Hello Nik, I tried your Planned % Completion formulae, but when we start tracking the progress of the project, 'Duration in days' is changing with respect to 'Actual Duration' in Tracking Table, but not the Baseline Duration. As a result my 'Planned % Complete' comes out to be wrong. Please tell me what change is to be done in the formulae.
Hi Pranav - In Step 2 we are using the baseline duration and comparing against the project status date. See the formula here
Formula: IIf(ProjDateDiff(Start,[Status Date])/480>=Val(ProjDurConv([Baseline Duration],pjDays)),Val(ProjDurConv([Baseline Duration],pjDays)),IIf(DateDiff(“d”,[Baseline Start],[Status Date])
@@niksprojects Yes Nik, I verified the formula, it states Baseline Start and Duration. But there is some issue with Baseline Finish or Actual Finish. When I put the Act Finish = BL Finish, the Planned % Compl comes out to be 100%. However, when I put Act Finish < BL Finish, P%C comes out to be more than 100%. Also, vice versa happens with Act Finish > BL Finish. Could you kindly check this issue in the Tracking Table, and get back to us. Thankyou.
I will have to take a look 👀, will post here if I see what’s the issue or recreate the problem
Hi thanks a lot for the tutorial! It works!
But, i have an error for milestone task that has 0 days. My Planned % Complete showing error and understandably so because any number cannot divide by 0.
So what should i do to tweak the formula?
Thankssss!
It’s a complex formula to take care of the milestone issue and is error prone, so I tweaked the logic completely and using duration- check out the link here niksprojects.com/product/planned-complete-formula-ms-project-file-template/
@@niksprojects tq! Will check it out 👍👍👍
Hi Nik,
Thanks for the great tips. How come my planned vs. complete comes at 152%?
Nik May thanks for uploading this video, I'm facing an Issue while monitoring the schedule.
The milestone activities are not getting updated the % planned progress.
How to resolve it, if you are having an solution for it pleas do let me know.
Many thanks in advance.
I had to revamp the full formula and I have posted on my website if you would like to download for a small fees. niksprojects.com/product/planned-complete-formula-ms-project-file-template/
Hi Nick, am getting an error on step 2 formular. It highlights ProjDateDiff. I tried retyping but I get the same error.
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
What is the "/480" for in the formula?
It is to convert product days in to minutes there are 8 hours a day which means 480 minutes
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 Nik, 1 question. How to track & view the scheduled/planned budget by month on Microsoft Project?
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, 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.
Hi Nick,I hope you are fine...I have problem,that when I insert column of duration in days after assigning baseline,my duration does not show in WBS,where may be sth wrong in my working please.
Thanks to you for present an excellent video. My problem is when I change duration as per my field condition that time Planned % complete value (-)% come. It is ok??
Thanks for the feedback. Please check that you don't have multiple baseline set and the status date and project start dates are set as well.
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.
Hi Niks, thanks a lot for this, but if I change status date sometimes it gives me negative % complete as elapsed days are negative. What can I do to solve this. Please respond if you have time
check out the latest template for download with updated formula - niksprojects.com/product/planned-complete-formula-ms-project-file-template/
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.
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.....
I'm still getting error on my Elapsed Days. even though I've set the reference point like u did... HELP! Some help is needed
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??
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?