none
Set task Work using custom formula RRS feed

  • Question

  • Greetings!

      I have a resource that produces with a fixed productivity, say, in samples per hour. Each task has it's corresponding resource productivity (custom field Number1) and number of samples (custom field Number2).

      Is it possible somehow to set standard Work or Duration field with the product of productivity value (taken from Number1) and corresponding number of samples (taken from Number2)?

    Thursday, February 16, 2012 7:09 PM

Answers

  • going this direction is not possible with a formula. You would need VBA code to do this.

    think about going the other way. It might work for you, it might not.

    Think about the formula for number of samples is a function of Work and Number1. Then you enter the Work and having the number get generated. Just a thought.

    the VBA would look something like this:

    Sub Setwork()
    Dim T As Task
    For Each T In ActiveProject.Tasks
        If Not (T Is Nothing) Then
            If T.Number1 > 0 Then
                T.Work = (T.Number2 / T.Number1) * 60
            End If
        End If
    Next T
    End Sub


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    • Marked as answer by Andrey Kazak Wednesday, February 22, 2012 7:40 AM
    Thursday, February 16, 2012 7:23 PM
    Moderator
  • It is possible to do this on a line by line basis however it does use paste links so is fairly manual but once set up does provide the automation you're looking for.

    1) set up number1 as Produced per day and number2 as Required

    2) Customise number3 to be [requried]/[produced per day]

    3) Copy / Paste link from number 3 for the row you're interested in to the work (in days) in that row.

    Note that you have to do this paste link for every row where you want this automation and you really want to comment the paste link in the notes field :)

    Hope this helps

    Miles

    • Marked as answer by Andrey Kazak Wednesday, February 22, 2012 7:39 AM
    Monday, February 20, 2012 2:04 PM
  • Andrey,

    Be advised that a VBA approach doesn't really have any downsides but using paste links does. Paste links are fragile, even when used within a single project file. They are prone to corruption.

    John

    • Marked as answer by Andrey Kazak Tuesday, February 28, 2012 7:21 AM
    Wednesday, February 22, 2012 3:33 PM

All replies

  • Only custom fields can have a formula in them. However you can use a VBA macro to do this.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management

    Thursday, February 16, 2012 7:14 PM
    Moderator
  • going this direction is not possible with a formula. You would need VBA code to do this.

    think about going the other way. It might work for you, it might not.

    Think about the formula for number of samples is a function of Work and Number1. Then you enter the Work and having the number get generated. Just a thought.

    the VBA would look something like this:

    Sub Setwork()
    Dim T As Task
    For Each T In ActiveProject.Tasks
        If Not (T Is Nothing) Then
            If T.Number1 > 0 Then
                T.Work = (T.Number2 / T.Number1) * 60
            End If
        End If
    Next T
    End Sub


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    • Marked as answer by Andrey Kazak Wednesday, February 22, 2012 7:40 AM
    Thursday, February 16, 2012 7:23 PM
    Moderator
  • Thank you Brian and Rod!

    However at the first stage for each task I'm about to define a productivity (samples per hour) and number of samples. Then I want project to calculate Work for each task. Third step I see in definition of Resources with corresponding Assignment Units with result Task Duration.

    Do you think it's impossible to implement such formalization without VBA macros?

    Friday, February 17, 2012 2:43 PM
  • Andrey,

    Let me put it a little more directly. Formulas in custom fields cannot create or edit standard default Project fields (e.g. Work, Duration, ResourceNames, etc.). Formulas can only calculate values for extra fields (e.g. Number1, Text1, etc.).

    So to answer your question, as Rod and Brian already stated, it is not possible to do this with a formula, manual entry or VBA is required.

    John

    Saturday, February 18, 2012 2:34 AM
  • Hi Andrew !

    1) You have to use material resources to calculate task output (number of samples) by using "Work" field

    2) As I understand you would like to calculate like "work"=Number1*Number2. You may assign not only one resource on task, so you have calculate this formula on ASSIGNMENT level  - thats why you have to use VBA programming.  "Work" does not primary belong to Task object, it belongs to Assignment object.

    PS: stable solution for bussines objectives you pointed is not easy one. It is required VBA programming

    Hope this help   


    Vadim Gerya, PMP

    Monday, February 20, 2012 8:31 AM
  • Hi Vadim,

    I feel I have to complete your reply.

    Work can also be a task field, and to use that field yyou need VBA just as well. No formulas for Project's scheduling engine field such as work!

    Greetings,

    Monday, February 20, 2012 2:02 PM
    Moderator
  • It is possible to do this on a line by line basis however it does use paste links so is fairly manual but once set up does provide the automation you're looking for.

    1) set up number1 as Produced per day and number2 as Required

    2) Customise number3 to be [requried]/[produced per day]

    3) Copy / Paste link from number 3 for the row you're interested in to the work (in days) in that row.

    Note that you have to do this paste link for every row where you want this automation and you really want to comment the paste link in the notes field :)

    Hope this helps

    Miles

    • Marked as answer by Andrey Kazak Wednesday, February 22, 2012 7:39 AM
    Monday, February 20, 2012 2:04 PM
  • Guys, thank you very much for detailed replies!

    I think of VBA script as the mainstream for my need. However, pasting links would an palliative option :)

    Wednesday, February 22, 2012 7:42 AM
  • Andrey,

    Be advised that a VBA approach doesn't really have any downsides but using paste links does. Paste links are fragile, even when used within a single project file. They are prone to corruption.

    John

    • Marked as answer by Andrey Kazak Tuesday, February 28, 2012 7:21 AM
    Wednesday, February 22, 2012 3:33 PM