I'm looking for a way to calculate the workload of resources using Microsoft Project. The workload needs to be calculated based on the urgency and complexity of the tasks assigned to the resource. I've managed to create custom Task Fields named Urgency and Complexity. I've also managed to create a custom Resource Fields named Workload. However, I couldn't find a way to use the value of Urgency and Complexity to calculate the value of Workload. Anyone here knows how I can solve this?
Sorry you are mixing fields and short of creating some code to do the math, you cannot use task and resource fields to calculate assignment fields (pairing of tasks and resources).
You may have luck posting to the Microsoft Forum for programming - but you need to have some skills in code development. The folks there will guide you - but it's unlikely they will write the entire code for you.
If you post there, please explicitly state what version of Project (2013, 2010, 2007) you are using.
MS Project has the ability to level according to task priority.
- Double click on a task to pull up the Task Information dialogue.
- In the "General" tab, look for "Priority." The default setting is 500. Change this to reflect the relative priority of the task. It sounds like you'll need to devise a way for priority to encompass both urgency and complexity.
- Go to the Resource tab in the ribbon.
- Click on "Leveling Options." Leveling Options is listed towards the right above "Clear Leveling" and "Next Overallocation." This opens the Resource Leveling settings dialogue.
- In the lower portion of the dialogue there is a "Resolving Overallactions" section.
- Set the "leveling order" to "priority, standard." The default is standard. Priority, standard will level tasks based on priority first, then according to the standard process.
I finally solved my problem. Many thanks to @JulieS for pointing me to the Microsoft Project forum. One particular user in that forum is very helpful. Here's the thread that I created to ask this question: How to use Custom Task Field value in Custom Resource Field Formula?
Anyway, it turns out that you can't mix task fields, resource fields, and assignment fields in Microsoft Project. At least, not normally. To be able to do that, the only possible way is to use VBA. Here's a run down of the things I'm doing to solve my problem.
First of all, I added two new task fields named Urgency and Complexity. The value of Urgency is set to use lookup tables with 3 values: "1 - Normal", "2 - Medium", "3 - Important". I also set the value of Complexity to use lookup tables with 3 values: "1 - Low", "2 - Medium", "3 - High". The numbers in the front of each value is used for calculating the workload.
Now that both task fields are defined, I simply add this VBA code:
Sub Calculate_Workload() Dim Reso As Resource Dim Job As Task Dim Task_As As Assignment Dim Reso_As As Assignment 'Reset Workload to 0 (zero) For Each Reso In ActiveProject.Resources Reso.Number1 = 0 Next Reso 'Calculate Workload For Each Job In ActiveProject.Tasks If Not Job Is Nothing Then For Each Task_As In Job.Assignments Dim Task_Urgency As Integer Dim Task_Complexity As Integer Dim Task_Workload As Integer Task_Urgency = Left(Job.Text3, 1) 'Urgency Task_Complexity = Left(Job.Text4, 1) 'Complexity Task_Workload = ((Task_Urgency - 1) * 3) + Task_Complexity Task_As.Number1 = Task_Workload Set Reso = ActiveProject.Resources(Task_As.ResourceID) For Each Reso_As In Reso.Assignments If Reso_As.TaskID = Job.ID Then Reso_As.Number1 = Task_As.Number1 Reso.Number1 = Reso.Number1 + Reso_As.Number1 End If 'TaskID Next Reso_As Next Task_As End If 'Nothing Next Job End Sub
The code above basically does these steps:
- Reset the workload for each resource (Reso.Number1).
- Retrieve the value of Urgency (Text3) and Complexity (Text4), i.e. the number in front of the value, for each task.
- Calculate the workload for each task based on the value of Urgency and Complexity.
- Set the value of Workload (Number1) for task assignment (Task_As.Number1) and resource assignment (Reso_As.Number1) so that it can be shown in the Task Usage or Resource Usage view.
- Sum all resource assignment workload to calculate the total amount of workload for each resource (Reso.Number1).
Now, every time there's a change to Urgency or Complexity, I simply run the VBA code and voila! I can now use the Resource Usage view to see the amount of workload for each assignment and the total amount of workload for each resource.
That's it. Hope this is useful.