MS Project actual vs expected

Recently when using MSProject and being asked to report on actual progress against expected from the current plan in use I did a bit of digging for an easy way to get the figures out of Project, this wasn’t as simple as one might expect.

Within the Gantt chart view you can add extra columns and there is default one for ‘% Complete’ but not for ‘Planned % Complete’, here’s how to get one

Right click on a Column and select the custom fields option

Select a field (I’ve used Text2 in the example below) and change the name

Select the formula option

Click the formula button and add in the following

Format(IIf(Val(ProjDurConv([Duration],pjDays))=0,0,(IIf([Status Date]<=[Start],0,IIf([Status Date]>[Finish],Val(ProjDurConv([Duration],pjDays)),Val(ProjDurConv(ProjDateDiff(Start,[Status Date]),pjDays)))))/Val(ProjDurConv([Duration],pjDays))),”0%”)

Ok the formula window (and the message about using the formula clearing existing values  if you get one)

Select the ‘Use formula’ option in the row below the formula button

MSP_CustomFields

Click OK

On the main toolbar click the project tab  then enter a status date
MSP_StatusDate

The planned completion uses the status date as the reference point for completion percentage, changing that will reflect the planned completion for that date

Now when adding a column the ‘Planned % Complete’ column will be available and the planned percentage will be displayed based upon the status date.

 

The formula:
Get the value for the task duration
Val(ProjDurConv([Duration],pjDays))

Get a value for the number of lapsed days
  IIf([Status Date]<=[Start],0,IIf([Status Date]>[Finish],Val(ProjDurConv([Duration],pjDays)),Val(ProjDurConv(ProjDateDiff(Start,[Status Date]),pjDays))))

Here we check to see if the Status Date we select is before or equal to the start date of the task
IIf([Status Date]<=[Start],

If it is we return a 0
If not we check to see if the status date is past the finish date of the task
IIf([Status Date]>[Finish]

If it is past the finish date we return the full task duration
Val(ProjDurConv([Duration],pjDays))

If not we return the difference between the task start date and the status date
Val(ProjDurConv(ProjDateDiff(Start,[Status Date]),pjDays)

Then we convert it all into a percentage
Format(VALUE,”0%”)

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s