# Calculation of duration from Quantity * Task time

• ### 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.

Friday, July 18, 2014 5:50 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 MPD As Single
MPD = ActiveProject.HoursPerDay * 60
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 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).

Friday, July 18, 2014 8:17 AM
• 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 MPD As Single
MPD = ActiveProject.HoursPerDay * 60
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 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 MPD As Single
MPD = ActiveProject.HoursPerDay * 60
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
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
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