Want "work" to be product of two custom columns. RRS feed

  • Question

  • Hi,

    I want to create a template Project file where the amount of work for each task will be the qty of items being made in that task and the time to make each item. (I'm building a scheduling tool for our fab shop).

    In my template:

    1. Column 1 can be the qty of items (left blank in the template, filled in for each new project). 
    2. Column 2 can be the known time our shop requires to make 1 of each item (not left blank, filled out in the template).
    3. Work should be column 1 * column 2.

    This seems way too easy. I know I can make formulas in custom columns/fields but I want a formula to fill in the Work field. Or, if I could drive the Finish field from the Start field and a custom field I make in place of Work?

    Let me know if you need more information or anything I should upload.



    Tuesday, February 28, 2017 9:37 PM

All replies

  • Ryan,

    You cannot control built-in fields (e.g. Start, Finish, Duration, Work, etc.) with a formula in a custom field. You could do it with a paste link but I don't recommend it since paste links are very prone to corruption. One safe way it can be done is with VBA, but, read the next paragraph.

    Your statement about wanting to drive the Finish field from a custom field instead of the Work field makes me wonder if you understand the difference between duration and work. In Project they are two different concepts. Duration is the difference between the start and finish as measured in working time. Duration is simply a span of time. Work on the other hand is the effort expended by one or more resources to perform the task. If a single resource works full time on a task then duration and work will be the same. If a resource works less than full time or there is more than one resource assigned to the task then duration and work will not be equal.

    So the question is, are you trying to drive the duration or are you trying to drive the work?


    Wednesday, March 1, 2017 12:05 AM
  • You can use the spare custom task fields to store the qty and qty_rate which is the number of items you can make per hour, for each item for a task where that task is: "make a of item x", and another field to calculate qty_rate*qty to get a duration. qty_rate is the inverse of your column 2.

    if you want to use your column 2 then the duration is column 2 (hours/item) x qty (items)

    That task would have to be for that particular item only. It gets more complicated if the task is "make a of item x and b of item y".

    It would be better to make each different item a work type resource and assign it in the necessary number of units to the appropriate task. MSP would then calculate the work in resource hours.

    Any help?

    Wednesday, March 1, 2017 3:41 AM