none
Calculation of duration from Quantity * Task time RRS feed

  • Question

  • I am trying to setup a schedule for a project where I know the time it takes make each widget and I know how many widgets and want to calculate duration from these two parameters.

    It would also be nice to have a second task where the number of widgets is the same (linked) but a different task time is used (paint widget).

    Also I have other tasks where the number is different (boxes of widgets, independent of multiplier) time to pack a box of widgets * number of boxes of widgets.

    I can make Duration1 = Number 1 + Number 2 but cannot link or place Duration1 into Duration.

    Any ideas, thanks in advance.

    Friday, July 18, 2014 5:50 AM

Answers

  • Graeme1961,

    Actually you can link the Duration1 field to the Duration field, but you really don't want to do that because it involves using paste links and those are notoriously prone to corruption.

    Rod and Trevor each proposed a good solution that will get you there, but personally I'd go with the VBA approach since it is much more flexible and less manual effort. The following macro will do what you need. Please note that Project calculates all time related parameters in minutes, so entries in the Number1 and Number2 fields will result in a value that may need to be converted if the desired units for the Duration field is "days". If the desired units are "minutes" then the conversion is not required.

    Sub WidgetCalculator()
    Dim t As Task
    Dim MPD As Single
    MPD = ActiveProject.HoursPerDay * 60
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If t.Summary = False Then t.Duration = (t.Number1 + t.Number2) * MPD
        End If
    Next t
    End Sub

    John

    • Marked as answer by Graeme1961 Sunday, July 20, 2014 9:36 PM
    Friday, July 18, 2014 2:47 PM

All replies

  • Project doesn;t make a good production calculator. To do this I think you will need a VBA macro (that is the only way to get Duration1 into Duration).

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Friday, July 18, 2014 8:17 AM
    Moderator
  • Let the calculation populate the duration1 column, then copy the duration1 column and paste it into the duration column?
    Friday, July 18, 2014 11:02 AM
  • Graeme1961,

    Actually you can link the Duration1 field to the Duration field, but you really don't want to do that because it involves using paste links and those are notoriously prone to corruption.

    Rod and Trevor each proposed a good solution that will get you there, but personally I'd go with the VBA approach since it is much more flexible and less manual effort. The following macro will do what you need. Please note that Project calculates all time related parameters in minutes, so entries in the Number1 and Number2 fields will result in a value that may need to be converted if the desired units for the Duration field is "days". If the desired units are "minutes" then the conversion is not required.

    Sub WidgetCalculator()
    Dim t As Task
    Dim MPD As Single
    MPD = ActiveProject.HoursPerDay * 60
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If t.Summary = False Then t.Duration = (t.Number1 + t.Number2) * MPD
        End If
    Next t
    End Sub

    John

    • Marked as answer by Graeme1961 Sunday, July 20, 2014 9:36 PM
    Friday, July 18, 2014 2:47 PM
  • Let the calculation populate the duration1 column, then copy the duration1 column and paste it into the duration column?

    Thanks Trevor,

    Yes that is the current solution, but obviously fraught with potential errors.

    Friday, July 18, 2014 9:26 PM
  • Graeme1961,

    Actually you can link the Duration1 field to the Duration field, but you really don't want to do that because it involves using paste links and those are notoriously prone to corruption.

    Rod and Trevor each proposed a good solution that will get you there, but personally I'd go with the VBA approach since it is much more flexible and less manual effort. The following macro will do what you need. Please note that Project calculates all time related parameters in minutes, so entries in the Number1 and Number2 fields will result in a value that may need to be converted if the desired units for the Duration field is "days". If the desired units are "minutes" then the conversion is not required.

    Sub WidgetCalculator()
    Dim t As Task
    Dim MPD As Single
    MPD = ActiveProject.HoursPerDay * 60
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If t.Summary = False Then t.Duration = (t.Number1 + t.Number2) * MPD
        End If
    Next t
    End Sub

    John

    Thanks John I will give it a try.
    Friday, July 18, 2014 9:26 PM
  • Graeme1961,

    You're welcome and thanks for the feedback. If this answers your question please mark it as the answer. You could also give Rod credit for the answer since he suggested VBA in his earlier response.

    John

    Friday, July 18, 2014 9:46 PM
  • Thanks John, this worked great.

    (after I fixed my original question  Number1 + Number2 should have been Number1(Qty) * Number2(Min ea).

    I also removed the MPD scaling item as the duration is already in minutes.

    And of Qty = 0 skip the calc , so you can enter a duration as normal.

    Graeme.

    Sub DurationCalculator()
    ' by John on M$ TechNet
    ' http://social.technet.microsoft.com/Forums/projectserver/en-US/ec83be4e-e073-44da-8c8e-506a005145f7/calculation-of-duration-from-quantity-task-time?forum=projectprofessional2010general
    Dim t As Task   ' Define scratch variable
    ' t.Duraton is task duration internal units = minutes
    ' t.Number1 = Qty
    ' t.Number2 = Min ea, duration in minutes per item
    ' Dim MPU As Single   ' define MinutesPerUnit as needed
    ' MPU = ActiveProject.HoursPerDay * 60 ' calculate Min per working day
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If t.Number1 <> 0 Then
                If t.Summary = False Then
                    t.Duration = (t.Number1 * t.Number2) ' * MPU
            End If
        End If
    Next t
    End Sub


    Sunday, July 20, 2014 10:20 PM
  • Graeme,

    You're welcome and thanks for the feedback. My widget broke a couple of years ago. Would you be so kind as to send me a new one, once you get your line up and running.

    John

    Monday, July 21, 2014 12:11 AM