Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Display Baseline Percent complete in MS Project

31 replies [Last post]
Damien Galllagher
User offline. Last seen 8 years 6 days ago. Offline
Joined: 18 Apr 2007
Posts: 7
Groups: None
Hello All,

In using MS Project I want to compare Percent Complete against Baseline (or Target) Percent Complete. Can anyone show me how to do this?
I have created a Basline Plan and updated project with percent complete, but just can’t get it to tell what should be complete, i.e. Baseline Percent Complete.

Any Suggestions welcome,

Thanks

Damien

Replies

Rashdan Rosman
User offline. Last seen 5 years 49 weeks ago. Offline
Joined: 4 Oct 2018
Posts: 2
Groups: None

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.

Tom Boyle
User offline. Last seen 4 weeks 5 days ago. Offline
Joined: 28 Nov 2006
Posts: 304
Groups: None

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  

 

 

 

   

S.M.Sajjad Haider...
User offline. Last seen 5 years 49 weeks ago. Offline
Joined: 13 Jan 2014
Posts: 10

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.

S.M.Sajjad Haider...
User offline. Last seen 5 years 49 weeks ago. Offline
Joined: 13 Jan 2014
Posts: 10

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.

sunil k
User offline. Last seen 11 years 22 weeks ago. Offline
Joined: 19 Sep 2011
Posts: 9

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

kumar s
User offline. Last seen 8 years 41 weeks ago. Offline
Joined: 26 Jan 2010
Posts: 159

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

S Z
User offline. Last seen 3 years 30 weeks ago. Offline

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,

Rahul Kupte
User offline. Last seen 11 years 11 weeks ago. Offline
Joined: 10 Oct 2011
Posts: 2
Groups: None

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

john Cohello
User offline. Last seen 10 years 21 weeks ago. Offline
Joined: 7 Feb 2011
Posts: 15

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

john Cohello
User offline. Last seen 10 years 21 weeks ago. Offline
Joined: 7 Feb 2011
Posts: 15

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

Raúl Vergara
User offline. Last seen 13 years 15 weeks ago. Offline
Joined: 2 Jul 2011
Posts: 2
Groups: None

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

sourena farahani
User offline. Last seen 13 years 3 weeks ago. Offline
Joined: 26 Apr 2011
Posts: 2
Groups: None

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 .

mukunda y
User offline. Last seen 11 years 4 weeks ago. Offline
Joined: 12 Nov 2008
Posts: 97
Groups: None
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
Roger Lim
User offline. Last seen 13 years 20 weeks ago. Offline
Joined: 20 Jun 2005
Posts: 3
hihi, i ve been learning a great deal here. Do they have books on formulas for MSP 2007?
Niek Zonneveld
User offline. Last seen 2 years 26 weeks ago. Offline
Joined: 17 Mar 2005
Posts: 188
Groups: None
I don’t doubt that this works technically, but I fear setup and maintenance is going to be a pain.

Cheers,

Niek.
mohd fakry
User offline. Last seen 5 years 27 weeks ago. Offline
Joined: 1 Oct 2006
Posts: 5
Groups: GPC Malaysia
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<
Darren Kosa
User offline. Last seen 7 years 34 weeks ago. Offline
Joined: 8 Feb 2008
Posts: 256
Groups: None
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
Dave Turnbull
User offline. Last seen 8 years 38 weeks ago. Offline
Joined: 16 Oct 2007
Posts: 17
Groups: None
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?
Darren Kosa
User offline. Last seen 7 years 34 weeks ago. Offline
Joined: 8 Feb 2008
Posts: 256
Groups: None
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
Dave Turnbull
User offline. Last seen 8 years 38 weeks ago. Offline
Joined: 16 Oct 2007
Posts: 17
Groups: None
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
Niek Zonneveld
User offline. Last seen 2 years 26 weeks ago. Offline
Joined: 17 Mar 2005
Posts: 188
Groups: None
There are no silly questions Jon.

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

Cheers,

Niek.
Niek Zonneveld
User offline. Last seen 2 years 26 weeks ago. Offline
Joined: 17 Mar 2005
Posts: 188
Groups: None
Jon,

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

Hope this helps,

Niek.
Jon Ward
User offline. Last seen 9 years 45 weeks ago. Offline
Joined: 16 Jun 2008
Posts: 60
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 Ward
User offline. Last seen 9 years 45 weeks ago. Offline
Joined: 16 Jun 2008
Posts: 60
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
Niek Zonneveld
User offline. Last seen 2 years 26 weeks ago. Offline
Joined: 17 Mar 2005
Posts: 188
Groups: None
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.

Nader K. Rad
User offline. Last seen 4 years 18 weeks ago. Offline
Joined: 2 Jan 2007
Posts: 46
Groups: None
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
Niek Zonneveld
User offline. Last seen 2 years 26 weeks ago. Offline
Joined: 17 Mar 2005
Posts: 188
Groups: None
My pleasure guys; I’m glad you found it useful!

Keep sharing and keep scheduling!

Niek.
A D
User offline. Last seen 3 years 48 weeks ago. Offline
Joined: 20 May 2007
Posts: 1027
Niek,

Thanks a ton. Never ever imagined to do that

Hemanth Kumar
User offline. Last seen 1 year 15 weeks ago. Offline
Joined: 1 Nov 2002
Posts: 260
Groups: None
Thank You Niek
Damien Galllagher
User offline. Last seen 8 years 6 days ago. Offline
Joined: 18 Apr 2007
Posts: 7
Groups: None
Niek,

Thank you for the solution, works perfect, no problems.


Thanks,

Damien
Niek Zonneveld
User offline. Last seen 2 years 26 weeks ago. Offline
Joined: 17 Mar 2005
Posts: 188
Groups: None
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!