Display Baseline Percent complete in MS Project

The easiest way to do is create another copy of the programme and named it baseline Programme and the other is Actual Work programme. After that in baseline programme, go to tab 'Project " and update the project to your cut off date or the progress plan date.Then, you can copy this column and paste it to your actual work programme.Hope it can help and have a good day.

S.M.Sajjad Haider...

This one will get you as close as you're going to get:

IIf([Status Date]<[Baseline Start],"0",IIf([Status Date]>[Baseline Finish],"100",(ProjDateDiff([Baseline Start],[Status Date])/ProjDateDiff([Baseline Start],[Baseline Finish]))*100))

3 key changes from your formula are:

1. Deleted the abs function and corrected the order of the dates in the ProjDateDiff functions. (By default the known earlier date comes first, and your IIf statement excludes any deviations.)

2. Got rid of the "[Project Calendar]" parameter, as it is superfluous and will give bad results for any task with a Task Calendar.  (With the parameter omitted, the function will default to the Task Calendar if one exists and to the Project Calendar if not.) 

3. Changed your denominator from [Baseline Duration] to the interval between the Baseline Start and Baseline Finish.  As a result, the planned %Complete will move linearly from 0% to 100% over the interval.  (Your denominator of "[Baseline Duration]" presumes a continuous baseline task, but yours has been interrupted by a long split.  The actual distribution of the baseline task's duration around the split was NOT saved with the baseline and has been lost.)

Good luck, tom  

Dear All,

I have a problem with my formula for Planned % field.

I am using follwoing formula:

abs(IIf([Status Date]<[Baseline Start],"0",IIf([Status Date]>[Baseline Finish],"100",(ProjDateDiff([Status Date],[Baseline Start],[Project Calendar])/[Baseline Duration])*100)))

It works fine & in use for almost a year now but  lately i have some suspended activities and for them its getting out of control & showing 2000% like value for planned field.

Kindly advise.

Dear All,

I have a problem with my formula for Planned % field.

I am using follwoing formula:

abs(IIf([Status Date]<[Baseline Start],"0",IIf([Status Date]>[Baseline Finish],"100",(ProjDateDiff([Status Date],[Baseline Start],[Project Calendar])/[Baseline Duration])*100)))

It works fine & in use for almost a year now but  lately i have some suspended activities and for them its getting out of control & showing 2000% like value for planned field.

Kindly advise.

sir,

i am new to this forum and learning alot from this.

I am not able to download the sample paper from the link provided by you.Can u please mail the same to me sunilkmrpl@rediffmail.com

Regards

Sunil.k

Hello Nader,

your formulae works excellent..thank you...

But i am facing one problem here.For the Milestone Activies it is showing as Error..I think this is because of the 0 duration since we are dividing 0/0 in the formulae..so is there any way to get milestone % to be shown correctly as well....

Thanks

Hi,



Can you please tell me that how can i calculate Baseline in excel sheet? what are the steps or formula?





I have an excel sheet that have baseline, then catch-up, then Actual Progress so i am making new sheet so want to know how can i calculate BASELINE?



Kindly share at kalbe00@hotmail.com





Thanks,

Hi....... Raul

Your Formula is Awesome.  But whenever i was update the project  0 to 100 % complete up to the status date from update of project field for the same baseline it shows the different % complete than by using your formula for baseline.

can you just help me for this .. and tell me one thing that  i wann to do some costomization for the same  i required  to do some formulization, is it any book for that ..... 

Rahul

Dear Raul,

Please help me on this,I have set the formula in the text1 and now it shows the comparision between basleine % complete and the actual progress %. Now for obtaining the actual % cmplete i assumed that all went according to the plan till the particular data date and used auto schedule,so my progress% complete should match with the baseline % complete right? but here its shows 16% as the progress and 20% as the baseline % complete respectively.How can this be so when i am using auto schedule .

Thanks in advance for any suggestions.

Cheers,

Walter

Dear Raul,

Please help me on this,I have set the formula in the text1 and now it shows the comparision between basleine % complete and the actual progress %. Now for obtaining the actual % cmplete i assumed that all went according to the plan till the particular data date and used auto schedule,so my progress% complete should match with the baseline % complete right? but here its shows 16% as the progress and 20% as the baseline % complete respectively.How can this be so when i am using auto schedule .

Thanks in advance for any suggestions.

Cheers,

Walter

Hi... Use this formula in text1 colunm (customize field):

Int(IIf([Current Date]>[Baseline Finish],100,IIf([Current Date]<[Baseline Start],0,ProjDateDiff([Baseline Start],[Current Date])/ProjDurValue([Baseline Duration])*100))) & "%"

Select in Calculation for task and summary row: use formula

For more tips... just write me to byrae@hotmail.com ;)

Rae

Hello All,



In using primavera 6 I want to compare Percent Complete against Baseline (or Target) Percent Complete. Can anyone show me how to do this?

Thanks .

Hi Neik

Can u help me



I applied ur Formula, Result is coming only for the Tasks Completed & not started. it is showing ERROR for the Ongoing Tasks(In progress according to baseline planned Schedule) I tried, but i am not understanding where the problem is.



Thanks

hihi, i ve been learning a great deal here. Do they have books on formulas for MSP 2007?

I don’t doubt that this works technically, but I fear setup and maintenance is going to be a pain.



Cheers,



Niek.

Guys,



I dunno whether this method would be easier or not, but this is how I would normally do it. To display baseline %complete in MS, it is just like in P3 where we would link a Target file to the Active one. Simply put it, in Microsoft Project, you could make a column to hyperlink to other column in a different file. Follow this steps :



Step 1 : Save a copy of your baseline program and rename it say Baseline%

Step 2 : Open that file and do tracking -> update project as complete through Sep08

Step 3 : Copy the %complete column by right-clicking the header

Step 4 : In the active/actual program, insert a (number) column and name it Baseline%

Step 5 : Right Click on the Baseline% header and choose paste special

Step 6 : Then choose paste link and press OK



You know you have done this correctly by seeing a triangle at the bottom right corner of each box.



So the next time you want to show baseline progress for Oct08, you would just update the Baseline% file as complete through Oct08 and the figure will automatically link to the active file. I hope this helps.







Regards,

m.fakry



>For more tips go to p3msp@blogspot.com<

Dave,



I don’t know of any documentation or training courses specifically targeted at Project Functions, it’s normally included in various guises as an addendum in advanced chapters. However, the arguments / statements used are similar to VBA so if you want to learn, it might be easier to approach it from that angle.



As a heads up on the different functions available, use MS Project Help and it’ll give you brief explanations for them all.



Regards,



Darren

Cheers Darren that works great, it is 2007 I am using. I would like to know more about using formulas with project, is there any books on it?

Hi Dave,



You don’t say what version you’re using. I’ll assume 98 - 2k3 and you can figure it out if you’re using 2k7 as it might be different.



In whichever custom field you used for the formula, if you go Tools > Customize > Fields you should notice some radio buttons under ‘Calculation for task and group summary rows’. If you select the ‘Use formula’ option, you should then see it roll-up to the summary tasks.



Regards,



Darren

Could anyone help, I have put the formula in as instructed and it works brilliantly, however it only displays the percentage for each individual task. I have tried displaying the number on summary bars but it does not work. it stays at 0. How do i get an overall percentage for the overall project and each summary bar?



Thanks

Dave

There are no silly questions Jon.



Please send me a private mail and I’ll forward you some user documentation for this.



Cheers,



Niek.

sorry if i’m being thick Niek,

i have inserted another column, highlighted that column, righ clicked and customised the field,, i have then chosen TYPE:Number and clicked okay, where do i think paste or type the code listed below??

i have tried to enter it in the cell but i get an error that reads: the value is not valid.



the number you entered is invalid or is outside the range for a number filed.



sorry about this,, very much appreciate your help



many thanks

Jon

Jon,



In the custom fields, under [tools] [customize] [fields].



Hope this helps,



Niek.

hi all,

really greatful for everyone to be posting the data that is shared within this thread,, sorry to be asking back-ward questions about thia, i have just moved over from P3 to MS Project and must say that i prefere the user interface of Project,, in regards to the topic of this thread,, i have created a baseline of my ’dummy’ project,, in idiot terms, where do i need to insert the formula that generates the Planned Percentage? am i missing something obvious?

many thanks

Excellent additions Nader!



Like I said: the examples were kept simple to show the principle, and you can make this as elaborate as you like.



Please keep sharing, this is what Planning Planet is all about. (i.m.h.o.)



Cheers and thanks again!



Niek.

Dear Niek;



Your formula would be completely fine tuned with these two changes:



1. adding support for task calendars

2. using weighted average for summaries (like %complete)



the first problem would be solved with this formula (number1):



IIf([Baseline Start]>[Status Date],0,IIf([Baseline Finish]<[Status Date],100,projdatediff([Status Date],[Baseline Start],IIf([Task Calendar]="None",[Project Calendar],[Task Calendar]))*100/projdatediff([Baseline Finish],[Baseline Start],IIf([Task Calendar]="None",[Project Calendar],[Task Calendar]))))



I could show you the problems this new formula can solve, if I could upload images. By the way, this formula would come in handy, if you have task calendars other than project calendar in some tasks.



for the second problem, we need some temp fields. First, suppose we have a Number2, which is [number1]*[baseline duration], with sum rollup for summaries.



Now we have to define Number3 as [Duration] with sum rollup.



our last field is Number4 with formula [number2]/[number3] and summaries should use the formula too.



Now, we have our real Baseline % Complete in Number4.

Tasks B%C in Number4 is equal to your Number1, but summaries are weighted averages.



You can download the sample file here:

http://www.khorramirad.com/bpc.rar

My pleasure guys; I’m glad you found it useful!



Keep sharing and keep scheduling!



Niek.

Niek,



Thanks a ton. Never ever imagined to do that

Thank You Niek

Damien,



You need to create a formula for that and introduce the concept of a status date.



This is a simple example formula which calculates the target duration % complete from the baseline, and which should reside in a custom number field:



IIf([Baseline Start]>[Status Date],0,IIf([Baseline Finish]<=[Status Date],1,ProjDateDiff([Status Date],[Baseline Start],"Standard")/ProjDateDiff([Baseline Finish],[Baseline Start],"Standard")))



Obviously, you need to have saved a baseline on all activities and you need to set the status date, otherwise the formula won’t work.



Since the output is in a number field you can roll-up the data to a summary task level, by using the average (this is in general more accurate than doing the calculation on the summary task).



When you want the % complete to be shown as a ’clean’ percentage you need to do a trick in MSP and place the following formula in custom text field:



Int([Number12]*100) & "%"



This cuts of any long fractions (so you don’t have 33.6666666666666666667% complete) and places the %-sign behind the obtained integer. (in this example custom field "Number12" holds the formula)



Good luck!