none
Work Field In Resource Sheet RRS feed

  • Question

  • The Work field in the Resource Sheet is calculated total hours for each resource based on the Duration in the Gantt chart. However, I have another field in the Gantt chart named Duration1 and I want Work field in the Resource Sheet to be calculated based on Duration1 not Duration. What formula can I use to customize Work field in Resource Sheet?

    Friday, December 6, 2013 10:46 PM

All replies

  • MeghanaBharucha --

    You cannot apply a custom formula to the Work field.  In Microsoft Project, the software wants to calculate one of three values:  Duration, Work, or Units.  These three fields are tied together permanently and cannot be broken up.  On a Fixed Units task, if you enter a Duration value, the software will ALWAYS calculate the Work value.  You cannot change this.  The best you might be able to do is to create a custom Number field containing a formula that calculates a value based off the Duration1 field.  Hope this helps.


    Dale A. Howard [MVP]

    Friday, December 6, 2013 10:51 PM
    Moderator
  • I understand. I will create another column with Work1. But what formula should I use? It should be same formula that's used to calculate Work from Duration, but here I just need to replace Duration with Duration1. 
    Friday, December 6, 2013 10:55 PM
  • MeghnaBharucha --

    Well, the second half of my original answer is wrong, and I apologize for giving you a wrong answer.  You are not going to be able to create a formula for the Resource Sheet view because neither the Duration or the Units fields are available in a Resource formula.  Again, I sincerely apologize.  Probably the best you can do is to use a custom Number field and manually enter a Work estimate in that field.  Hope this helps.


    Dale A. Howard [MVP]

    Friday, December 6, 2013 11:04 PM
    Moderator
  • Hmm, I think a bit of VBA maybe able to achieve what you're looking for (don't see a way of doing it with a formula), at a high level, I think the easiest way to achieve this would be to build a routine which at a high level:

    • Steps through each task with assignments
    • Stores the value of the Duration field in a variable
    • Overwrites the Duration field value with the Duration1 value
    • Recalculate the task
    • step through each assignment in the task
    • Take the resulting value from the Work field and put it in a custom field for the assignment
    • Reinstate the original task duration (or simply undo) to revert back to the original state of the schedule

    There are lots of nuances that would need ironed out (what if the new duration breaches a constraint? what if the assignments have a custom profile applied to their work contour? As there are two sets of custom fields for assignemnts: task.assignment and resource.assignment, atw aht point do we align these?), but I think it's the easiest way.

    It's definitely beyond the realms of a formula, as to calculate the duration, Project doesn't just look at the work value, it can also be impacted by:

    • The resource calendar of the assigned resourced
    • The task type (fixed duration, fixed units, etc...)
    • Whether the task is effort driven.
    • The calendar of the task/project

    Additionally, you're talking about taking a value from the Gantt view and using it to calculate a value in the Resource sheet view, but the relationship between resources and tasks is many to many (i.e. one resource can be assigned to multiple tasks, and one task can have multiple resources). So the routine would actually need to look at the values at assignment level to calculate for the resource sheet.

    An additional complication is that while there are custom duration fields (Duration1 to Duration10), but there are no custom Work fields. You'd need to use either:

    1. A duration field, but give it a custom title of "Work1" or similar. This means you could have the "hrs" suffix on the field in the column itself, but you may run up against the maximum value limit for this field type, 72,270 days. While it seems unlikely, if you're working on a large programme and using generic resources, i.e. "Engineer" rather than "Andrew Simpson, Engineer", then the hours can mount against these resources.
    2. A number field - this doesn't display the "hrs" suffix, although it has a higher limit at 9,999,999,827,968.

    So lots to think about - if these constraints sound workable to you, pick a custom field and I'll knock something together to get you started.

    Andrew

    Sunday, December 8, 2013 9:56 PM
  • MeghnaBharucha,

    After I first read your post and Dale's replies, I immediately thought of using VBA to accomplish whatever you need. And Andrew did just that in this pseudo-code outline in his response, but in my mind the question that hasn't been asked is, what is your end goal? What exactly are you trying to do with the Duration1 field and why is the normal Project scheduling algorithm using the Duration field not adequate for your needs?

    Until we have a better understanding of the above, none of us can give any kind of definitive answer.

    John

    Monday, December 9, 2013 4:30 PM
  • Hello Andrew & John,

    Sorry for my delayed response and thank you for your detailed response!

    VBA is bit complicated for me with lack of experience in writing macro. Even if we go about recording, the steps are not easy for determing Work1 for each resource off of Duration1 field since Work for each resource in Resource Sheet is calculated from all the tasks associated with that resource.

    I have both Duration and Duration1 fields because Duration is # of hours given to complete the task and Duration1 is # of hours it should take to complete the task. I have Duration1 also because it may not be same as Actual Duration.

    Duration1 would be used to calculate Work1 to project total # of hours it should take for each resource for the given project. We can also use this field to project total resource cost and then compare it with the actual duration and cost.

    I hope this answers the question on the end goal. Please guide me accordingly.

     

    Friday, December 13, 2013 2:35 PM
  • MeghnaBharucha,

    I'm having a little trouble understanding the difference between the "number of hours given to complete a task" and the "number of hours it should take to complete a task". It sounds like the latter is really the work content. For example, with a fixed duration type task, a task may have two weeks to be completed but it may only take one week of work. The "given" duration is 2 weeks, the expected (estimated) work is 40 hours, (sometime during that 2 week period), and the actual work is what really happens. So in your case, how is duration versus duration1 different?

    Also, just skimming through the previous responses in this thread I don't see any mention of using a baseline. You can set up a plan and save multiple baselines, each of which will have a calculated baseline work value. That would be a lot easier than writing a macro.

    John

    Friday, December 13, 2013 3:58 PM
  • John,

    Your example is exactly what we want, but if the content of Duration1 is entered as Work then it changes the Start and Finish Date. For ex, I am entering 2 weeks for Duration from 12/13/13 as the Start and 12/27/13 as Finish, but then I enter only 1 week for Work (because task requires only 40 hours of work) then the Finish Date would change to reflect 1 week only even though I want to give them anytime during the 2 weeks to work on it.

    Friday, December 13, 2013 4:04 PM
  • MaghnaBharucha,

    No, I think you misunderstood. If the task type is set as Fixed Duration, then you set the duration as 11 days (12/13/13 through 12/27/13) and the Work as 40 hours. The duration will NOT change because it is a fixed duration task.

    Add the Type field to your Gantt Chart view. You will see the default is Fixed Units. Change that to Fixed Duration. Or you can do it via Task/Properties group/Information icon/Advanced tab.

    If that is indeed what your after, then this is an extremely simple answer to your issue.

    John

    Friday, December 13, 2013 5:16 PM
  • John,

    Thanks, it does help me somewhat! But now is it possible for me to keep the Duration constant when Actual Duration data is different?

    I would also like to compare Duration field from the project planning and the Actual Duration after implementation. However, when I enter data in the Actual Duration, it changes data in the Duration to make them same.

    Friday, December 13, 2013 7:54 PM
  • Hi MeghnaBharucha,

    Indeed this is a common behavior of Project: while entering any actual information, it will overwrite planned information.

    I didn't read all the comments, but if you want to keep track of the initialy planned duration, wouldn't you use the baseline fonctionality? Then at any time you'll be able to compare the current plan with any of your 11 baselines.

    Please find below a link that explain this key MS Project feature:

    http://office.microsoft.com/en-us/project-help/set-and-save-a-baseline-HA102809638.aspx

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Friday, December 13, 2013 8:04 PM
    Moderator
  • So now I have 3 fields: Baseline Duration - time given to work on the task, Work - time it should take to complete the task, and Actual Duration - time it takes.

    Baseline Duration and Actual Duration are fixed Duration task type; however, when I enter data in the Actual Duration, data in the Work also changes to match the two column.

    Friday, December 13, 2013 8:17 PM
  • Hi,

    A few comments:

    • Baseline duration : duration you commit to work on the task : will not be changed 
    • Duration : planned duration : will be updated on the regular basis based on the team updates
    • Actual duration : time the task actually took to be complete. Will be equal to the duration once the task completed.

    Then the task type is defined for the task and not for each duration.

    Actually you should not enter the actual duration. Whenever the task will be marked as completed, the actual duration will be updated automatically.

    Depending on how you parameter the task type, the actual duration might influence the work. For example if I have a 1d fixed duration task assign to me (thus 8hrs of work) and marked as completed, actual duration will be set automatically to 1d. Then if I change the actual duration to 3d, the duration will change also to 3d, thus the work will be equal to 24hrs. This is a normal MS Project behavior.

    I'd finally said that changing the actual duration must not occur. Actual duration means that the task is completed, thus it must not be updated anymore.

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Friday, December 13, 2013 8:38 PM
    Moderator
  • MeghnaBharucha,

    Actually you do NOT want the Duration field to stay constant. When the plan is first created, you should save a baseline. That captures the original plan. As the plan is executed and actual data entered, the current schedule is automatically updated to reflect reality. That's why the Duration, Start and Finish fields change when actual values are entered. That is normal and desired action in a dynamic plan.

    Then when you want to compare the original plan with the current plan, use the Tracking Gantt view. It will display the baseline data and the current data so differences are visible. It also displays several variance fields.

    John

    Friday, December 13, 2013 9:22 PM
  • Thanks guys! Since, there is no way to keep Duration different from the Actual Duration, I baselined Duration. Now, similarly, I would also like to baseline Work; however, baselined Work doesn't reflect input values of Work field. Also, just how Duration and Work data are different in my plan, Actual Duration and Actual Work may also differ. I was able to keep the Work values different from the Duration by changing the Duration task type. Is there a way to fix Actual Duration as well so that it's not same as Actual Work?

    Duration = allocated duration to complete the task [Task Type: Fixed Duration]

    Work = time it should take to complete the task [Different from Duration]

    Actual Duration = taken duration to complete the task

    Actual Work = time it took to complete the task

    Also, in Resource Sheet, is there a way to compare Baseline Work with Actual Work for each resource as well as Baseline Cost based on Baseline Work with Actual Cost based on Actual Work?

    This would allow us to work in the same plan while comparing the plan (by Start/Finish, Work in Gantt, Work in Resource Sheet, and Cost) before and after implementation. If not, then I guess we can have two copies of the plan (one for before implementation and the other for after) with Duration and Work where task type is fixed Duration.

    Monday, December 16, 2013 4:03 PM
  • Thanks guys! Since, there is no way to keep Duration different from the Actual Duration, I baselined Duration. Now, similarly, I would also like to baseline Work; however, baselined Work doesn't reflect input values of Work field. Also, just how Duration and Work data are different in my plan, Actual Duration and Actual Work may also differ. I was able to keep the Work values different from the Duration by changing the Duration task type. Is there a way to fix Actual Duration as well so that it's not same as Actual Work?

    Duration = allocated duration to complete the task [Task Type: Fixed Duration]

    Work = time it should take to complete the task [Different from Duration]

    Actual Duration = taken duration to complete the task

    Actual Work = time it took to complete the task

    Also, in Resource Sheet, is there a way to compare Baseline Work with Actual Work for each resource as well as Baseline Cost based on Baseline Work with Actual Cost based on Actual Work?

    This would allow us to work in the same plan while comparing the plan (by Start/Finish, Work in Gantt, Work in Resource Sheet, and Cost) before and after implementation. If not, then I guess we can have two copies of the plan (one for before implementation and the other for after) with Duration and Work where task type is fixed Duration.

    A few comment: 

    baselined Work doesn't reflect input values of Work field : when you save a baseline, the behavior is the same for all data copied in the baseline fields. The current value is stored is the corresponding baseline field. Ie when saving baseline n, work is copied in the baselinework n and duration is copied in the baselineduration n.

    Actual Work = time it took to complete the task : actual work is the effort and not the time it took to complete the task

    is there a way to compare Baseline Work with Actual Work for each resource :  if you split your screen and display the task form with work information, you'll see for each assignment work, actual work, baseline work, remaining work. Another way, if you need it timephased, is to use the resource or task usage views and add the baseline, actual and planned work (double clicking the right part of your screen).

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Monday, December 16, 2013 4:15 PM
    Moderator
  • Also, despite fixing task type by Duration, for some tasks, I can't change Work to be different from the Duration. For ex, Duration is 1 wk and so is the Work 40 hr. But if I change Work to 0 because the task is external and there is no internal hour needed for it, it reduces project Duration by 1 wk which is not true because project progress depends on that external task.
    Monday, December 16, 2013 4:15 PM
  • I would advice you to read this useful post to fully understand how project calculates tasks parameters and the key concept of effort versus duration:

    http://office.microsoft.com/en-001/project-help/how-scheduling-works-in-project-HA010373148.aspx

    If duration and work are different, it means that there is more or less than 1 FTE working on the task. When 1 FTE is working on the task (unit = 100%), work = duration.

    Talking about external tasks, I'd suggest to create a milestone (duration and effort equal to 0) that would be an predecessor (entry point) for your internal task.

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Monday, December 16, 2013 4:22 PM
    Moderator
  • I have another question regarding assignment unit.

    I have several tasks where I have assigned more than 1 resource (R).

    For ex,

    Weekly internal meeting          1hr          R1, R2, R3

    In this case, each one has to attend/participate in the meeting 100%. So in the Resource Sheet>Work, I should see 1 hr for each of these 3 resources. However, instead it divides 1 hr among 3 resources.

    How can I resolve this?

    Monday, December 16, 2013 6:26 PM
  • Hi MeghnaBharucha,

    I posted a link to a useful post, which explains Project calculation. It should bring some light on your question.

    This is related to "effort driven" functionality. I think that first you assigned 1 unique resource on the task, which made 1h duration and 1h work.

    Then you assigned 2 more resources. Since the task was probably effort driven, the work (1h) was splitted among between the first resource and the 2 others.

    If you don't want Project to do that, uncheck "effort driven" before adding the 2 others resources, and Project will add 1h work for each added resources. Meaning the task will be 1hr duration for 3hrs work.

    First screenshot with effort driven:

    Second screenshots without effort driven:

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS



    Monday, December 16, 2013 6:33 PM
    Moderator
  • Thanks for the screen shots even though they are in French. I unchecked effort driven box and still it doesn't do what you're showing here.

    Monday, December 16, 2013 6:45 PM
  • The effort driven functionality is relevant only when you assign additional resources on an existing assignments (a task with already a resource on).

    Thus it is normal that just unchecking the option, it does nothing.

    Recreate your task ensuring the option "effort driven" is uncheck and you'll have the same behavior than in the second screenshot. Or remove all assignments on the task, uncheck the option and assign again your 3 resources.

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Monday, December 16, 2013 6:48 PM
    Moderator