none
Apply a formula on an assignment? RRS feed

  • Question

  • Hello everyone,

     

    I try to display in a column the Daily Man Rates (other than the default Standard Rate relative to the Cost Rate Table A) without any macros...

    So in the "Task Usage" view, I created a custom field named "Daily rate" with the following formula : 480 * [Cost] / [Work]

    I can apply it on summary tasks but not on an assignment level. And if I roll up the formula on the assignment level, the result is incorrect for all the tasks with multiple assignments.

              (Wanted to post a picture but it does not work for the moment...)

    Is it possible to have a formula on an assignment level?

    How could I display my cost information?

     

    Thanks in advance!


    • Edited by SoA Wednesday, December 7, 2011 6:45 PM
    Wednesday, December 7, 2011 6:43 PM

Answers

  • There is no assignment category of the custom fields. The only way is to use VBA code. A small VBA routine can be used to populate the custom field at the assignment level in a Usage view. The same topic has been discussed before in this forum, pls search for it.
    • Marked as answer by SoA Thursday, December 15, 2011 1:02 PM
    Wednesday, December 7, 2011 8:09 PM

All replies

  • There is no assignment category of the custom fields. The only way is to use VBA code. A small VBA routine can be used to populate the custom field at the assignment level in a Usage view. The same topic has been discussed before in this forum, pls search for it.
    • Marked as answer by SoA Thursday, December 15, 2011 1:02 PM
    Wednesday, December 7, 2011 8:09 PM
  • It's here at: http://social.technet.microsoft.com/Forums/en-US/projectprofessional2010general/thread/57bc6621-6698-4a5e-bafb-51664cc392b0
    Wednesday, December 7, 2011 8:11 PM
  • Thank you for your help, Project User.

     

    I tried to find a solution without macro...

    Thursday, December 15, 2011 1:03 PM
  • You're welcome...
    Thursday, December 15, 2011 1:50 PM
  • Hello SOA,

    Sorry for bumping in here -- but I don't understand the math of what you are trying to do.  The cost you are referring to in your formula is the task cost which is calculated by Work*Standard rate.  If the resource is assigned at less than a days worth of work -- do you really want to count the full day?

    I did test your formula in the Task Usage view with multiple resources assigned and selected the option "roll down unless manually entered" and it appears to work fine.

    I am working with Project 2010, SP-1.

    I hope this helps.

    Julie

    Thursday, December 15, 2011 2:14 PM
    Moderator
  • Hello Julie,

     

    Here is what I try to show. Sorry the fields are in french :

    Task mode / Task Name / Cost Rate Table / Work / Cost / task custom field named representing the standard rate depending on the table I chose...

    The formula I created is in the field "Taux unitaire" (like standard rate but for other cost rate tables).

    In this example, I did not roll down the formula. But If I do it, it will work for T2 and T3 (because I have only one resource) and I will have the same "360 €" on toto and titi assigned on task T1, which is wrong  :/


    • Edited by SoA Thursday, December 15, 2011 5:23 PM
    Thursday, December 15, 2011 5:19 PM
  • Ah, I tested your example in a number field, not one of the extra cost fields.   I am getting inconsistent results now using either a cost or number field when I switch to different rate tables.  

    I'm still struggling with what you are trying to accomplish.  Are you trying to calculate a daily rate for the resource regardless of how many hours they work?

    Julie

    Thursday, December 15, 2011 8:17 PM
    Moderator
  • Yes, I want to show the daily rate depending of the table choice, regardless of the workload.

    And I can not use the "Standard Rate" filed because it's only linked with the Cost Rate table A...

    Friday, December 16, 2011 6:08 PM
  • Hi SoA,

    Sorry for the delay getting back to you.  Honestly, I don't see a way to do this in Project without resorting to VBA.  The issue as I understand is you really want a cost per day of task duration -- not related to work.  So if resource A is working 2 hours per day on a 5 day tasks (10 hours), you really want to calculate the cost for 8 hours per day for a 5 day task (40 hours)?

    I cannot see how to accomplish that.  Duration is a task field and is not available on the assignment level.

    Short of VBA, I'd suggest pulling the data out to Excel and then creating formulas there.  I realize the issue is that you lose the ability to change rate tables and recalculate.

    Saturday, December 17, 2011 3:29 PM
    Moderator
  • The issue as I understand is you really want a cost per day of task duration -- not related to work.  So if resource A is working 2 hours per day on a 5 day tasks (10 hours), you really want to calculate the cost for 8 hours per day for a 5 day task (40 hours)?

    Hello Julie and thank you for the time spending on this forum,

    I am not interested in duration at all. I just want to show the daily man rate of the resource regarding the Cost Rate table I choose.

    In your example, if a resource works 2 hours per day on a 5 days task (10 hours), yes I want to show the cost on a daily base (in my case, for 8 hours). I would face the same issue if I wanted to show it on a hourly base, no? Our PMs are used to speak about man days work and to compare resource costs on a daily base. So if the project cost exceeds the given budget, the PM could switch one of the resources with a lower "Standard rate".

    But once again, the duration field is not part of the equation...

    Is the finality of my request more clear?


    • Edited by SoA Saturday, December 17, 2011 5:06 PM
    Saturday, December 17, 2011 5:06 PM
  • You're welcome SoA for what little help I've been.  I don't think I've shed too much light thus far :-)

    I believe I do understand what you mean, but the problem is that Project calculates costs per task by work * rate. The cost of work on each task and each assignment should change when you change cost rate tables and that is the true cost to your project.  In the scenario I gave, if a resource is only working 2 hours per day on a 5 day task -- the cost to the project is only 10 hours -- not 40 hours.

    If the resource is on rate table A at $40 per hour the cost will show $400.  If I switch to another rate table at $30 per hour the cost adjusts to $300 based upon the 10 hours.

    The only way I can see to reflect what you are asking for is to make sure that if a resource is assigned to a 5 day duration task (as in your example -- T1)that you intend for the resource to work the full 5 days or 40 hours.  If not, T1 should be two tasks -- one with toto for 3 days and one with titi for 2 days.

    Again, the calculated cost does change if you change cost rate tables -- I think the only way to get what you truly want is through VBA code.

    I hope this helps.

    Julie

    Saturday, December 17, 2011 11:47 PM
    Moderator