none
Use custom fields to calculated work RRS feed

  • Question

  • I'm managing several projects rolling out the same system to different locations and I'm using a copy of my current MS Project file as a starting point for developing the new cost and schedule.  The work effort for many of the tasks is depending upon the number of screens we're developing for that specific rollout or the number of pieces of equipment we'll be hooking up.  Is it possible to use custom fields to calculate the work involved for a specific task?  Here's a bit of additional detail....

    For a specific project I may be creating 50 screens and we estimate that it will take 1 1/2 hours per screen, so the task would have a total work effort of 50 * 1.5 = 75 hours.  The way I'm doing it now is I'm inputting details in the notes saying something like (estimating 50 screens taking 1.5 hours per screen for a total work of 75 hours) and then just typing 75 hours in the work field.  If there's any way possible, I'd like to be able to have a column where I can input the number of screens (i.e. 50) and another field with the per unit effort (1.5) and have it automatically update the work column with the calculated work effort if it applies or allow me to input the work effort if it shouldn't be calculation based (the way MS Project already works).

    Anybody have any thoughts and if / how I could do this?

    Wednesday, May 23, 2012 2:26 PM

Answers

  • Hello,

    You could do this with a couple of custom fields and some vba.

    Add the following custom text fields into your gantt view, and rename them as detailed below:

    • Number1 - Effort per Unit (Hours)
    • Number2 - No. of Units

    Then put the following code into a VBA module:

    Sub CalculateEffortFromUnits() Dim pj As Project Dim t As task Set pj = activeProject 'step through each task in the current schedule. For Each t In pj.Tasks 'first that neither of the field values are zero If t.Number1 * t.Number2 <> 0 Then 'if both are populated, update the work value t.Work = t.Number1 * t.Number2 * 60 End If 'if either of these two field values are zero, 'leave the work value alone 'move on to the next task Next t End Sub

    When you execute the macro, it will refresh all the work values in the schedule in line with the unit estimate/no. of units, as shown below:

    Hope this helps,

    Andrew

    • Marked as answer by Houstonclan Wednesday, May 23, 2012 3:56 PM
    Wednesday, May 23, 2012 2:44 PM

All replies

  • Houstonclan,

    What you are basically asking is to have the Work field act like an extra field such that it is customizable. Project is not set up to do that. You could use your custom field to calculate the work value and then paste link it to the Work field but paste links are very susceptible to corruption so although this is a possible solution it is definitely not a recommended one.

    A more viable but complex solution is to use a VBA approach wherein you enter the values as stated and then run a macro to set up the Work field accordingly.

    Personally I think you approach of using the Notes field is excellent. The Notes field was designed for capturing supporting information about tasks, resources, etc.

    John

    Wednesday, May 23, 2012 2:35 PM
  • Hello,

    You could do this with a couple of custom fields and some vba.

    Add the following custom text fields into your gantt view, and rename them as detailed below:

    • Number1 - Effort per Unit (Hours)
    • Number2 - No. of Units

    Then put the following code into a VBA module:

    Sub CalculateEffortFromUnits() Dim pj As Project Dim t As task Set pj = activeProject 'step through each task in the current schedule. For Each t In pj.Tasks 'first that neither of the field values are zero If t.Number1 * t.Number2 <> 0 Then 'if both are populated, update the work value t.Work = t.Number1 * t.Number2 * 60 End If 'if either of these two field values are zero, 'leave the work value alone 'move on to the next task Next t End Sub

    When you execute the macro, it will refresh all the work values in the schedule in line with the unit estimate/no. of units, as shown below:

    Hope this helps,

    Andrew

    • Marked as answer by Houstonclan Wednesday, May 23, 2012 3:56 PM
    Wednesday, May 23, 2012 2:44 PM