Thursday, March 13, 2014

Using PERT to calculate task duration in Microsoft Project

What is the PERT technique?
Following the definition found in Wikipedia, "the Program (or Project) Evaluation and Review Technique is a method to analyze the involved tasks in completing a given project, especially the time needed to complete each task, and to identify the minimum time needed to complete the total project."

For those who passed or are preparing for the PMP exam, you should know the famous formula to calculate the expected duration of a task. This is also known as the "three point estimate".
Calculation of the expected time (TE): the best estimate of the time required to accomplish a task, accounting for the fact that things don't always proceed as normal (the implication being that the expected time is the average time the task would require if the task were repeated on a number of occasions over an extended period of time). The formula is:

TE = (O + 4M + P) ÷ 6

How to use this formula in Microsoft Project 2013 to calculate task duration (or work)
There might be other solution(s) to do it (I don't know any so I'm open to your suggestion :)), but I will present you how to update the task duration using a macro.
Note that you can also use the same technique to calculate task’s work (or any other task properties by the way).

How to implement this?
First of all, we will create a project plan with 3 tasks. For this demo, tasks will be manually scheduled; we don't need to link tasks neither assign resources.

Now, to be able to calculate the duration using PERT technique, we need to allow the input of 3 values for:
·         Optimistic duration
·         Pessimistic duration
·         Most likely duration

We have to create 3 Custom fields, using field "Task" and type "Duration". Right click on any column headers, and select Custom Fields.

Rename Duration 1 to 3 as follow:
·         Duration 1 = "Optimistic duration"
·         Duration 2 = "Pessimistic duration"
·         Duration 3 = "Most likely duration"

You now need to display the 3 fields on the Entry table, to let you input data. Right click on a column header and select Insert Column. Your columns can be found either under the generic name (Duration1 to Duration3) or with the updated name.

 Now let's create the macro
To create Macro, you have to open the Visual Basic editor. Click on the View tab and under Macros, select Visual Basic. You can also press Alt + F11.

On the left hand side, double click the current project "ThisProject" and paste the following code.

Sub PERT()
'Declare variable
Dim myTask As Task

'Loop all tasks to update duration
For Each myTask In ActiveProject.Tasks
If Not (myTask Is Nothing) Then
       'You might want to add here another check on % complete, not to update started tasks
       'E.g. If (myTask.PercentComplete = 0 & myTask.PercentWorkComplete = 0) Then

       'User PERT formula to update Duration, knowing that Duration1 = Optimistic duration...
       myTask.Duration = (myTask.Duration1 + 4 * myTask.Duration3 + myTask.Duration2) / 6
              'Extremely simple error handling, you might need to replace it
              MsgBox Prompt:="Error on calculating duration"
       End If
Next myTask
End Sub

Note that this sample code is given as information, this does not contains a proper error handling and should be reviewed before using it in production environment.

We can execute the macro
You may input some values on the 3 fields for our tasks.

Then to execute the macro you have 2 options:
Option 1: Using the View macro and Run.
Click on the View tab and under Macros, select View Macros. You can also press Alt + F8.

On your Macros’ list, select the PERT one (named <filename.ppt>!PERT) and click Run.

Option 2: Adding a button on the Quick Access Toolbar (my favorite option)
On the Quick Access Toolbar, click on the option button and select More Commands…

On the customization screen, select Macros in the “Choose commands from” list item.
Select your PERT macro and click Add >>
You can change the icon or modify the link name by clicking on the Modify… button.

Your link appears on the Quick Access Toolbar.

Click on it to play the macro and update the tasks duration.
Our three tasks’ duration is updated

Security aspect
As macros are considered as potential security concern, you might see the following warning when you open your project plan. If you want to use the macro, you have to “Enable Macros”.

Note that this is a configuration (common to all Office tools). To change the behavior of Project regarding macros, click on the File tab, then Options. On the Trust Center section, click on Trust Center Settings…

By default, the macro settings is set to “Disable all macros with notification”; you can change this option.

To digitally sign your macro, you can refer to this article Digitally sign a macro project on Microsoft website.

11 commentaires:

Binita said...

Nice post related to itil it will certainly help for my MSP Training.

Mauricio said...

Very useful information. Thank you.

Jeremy Cottino said...

Many thanks Mauricio

K-milo Vargas said...

Thanks for the post... i´m trying to do the same thing with the work, i mean, i´m want to calculate the work of a task using the pert method. But i don´t know if i can change the work using the script that you post (changing the sentence "myTask.Duration=" for "myTask.Work=" )....i hope that i´m explaining myself regards

Jeremy Cottino said...

Yes, just change with myTask.Work = (myTask.Duration1 + 4 * myTask.Duration3 + myTask.Duration2) / 6
It's working fine with this.

K-milo Vargas said...

Thanks Jeremy, but it doesn´t work, perhaps i´m do something wrong. When I replace the sentence and run the macro, the system throws me an error 1101. If you don´t mind, would you like to share me the script that works for you.


Jeremy Cottino said...

Sub PERT()
Dim myTask As Task
For Each myTask In ActiveProject.Tasks
If Not (myTask Is Nothing) Then
myTask.Work = (myTask.Duration1 + 4 * myTask.Duration3 + myTask.Duration2) / 6
MsgBox Prompt:="Error on calculating duration"
End If
Next myTask
End Sub

Anonymous said...

I tried changing myTask.Duration to myTask.Work but still got the 1101 error. I'm wondering if it has something to do with the mismatch in column types (i.e. we're using duration columns to calculate a value and then plugging it into a work column). Any help would be appreciated.


Jeremy Cottino said...

Made de test with this formula and it worked for me:
myTask.Work = (myTask.Duration1 + 4 * myTask.Duration3 + myTask.Duration2) / 6
Error 1101 is related to your code, you might rule out all mistakes

Francis Almeida said...

Very good. I made my activities in project 2013.

Jim Lauffer Jr said...

Hi Jeremy, Wow this is awesome!! Works great and I am using MS Project 2010..

Post a Comment