SOLVED

Number of assigned resources on tasks

Copper Contributor

Someone know how to set up a new column to show me the number of assigned resources on tasks?

In column Tekst 1 i have drawn numbers in red just for an example.

And also i would like to have a column that converts hours into days.(column Tekst 2)

Skjermbilde.PNG

24 Replies
Polestar2021, The custom field for resource count you want can't be done with a custom field formula, VBA is required to parse the Resource Names field to give a count. As far as converting the duration in hours into days, I need to know your definition of a day. The default is 8 hours (working time) but when I apply that to the example task you show (i.e. red 19), the default value doesn't convert. John
Hi, and thank you for answer!
I have set the working time for 7.5 hours into days(37.5 in weeks)
Polestar2021,
Thanks for the information but I still don't get 19 days for 315.44 hours. Is the red 19 just a placeholder and not meant to represent a real value?
John
sorry, red 19 is put on wrong place, i think the correct number will be 42, because 315 divided on 7,5 is 42
Polestar2021,
Okay, that now makes sense. I'll work on it and post back when I have something.

A side question, what is the reason you want a separate field to show the day equivalent for the hours in the Duration field? If you indeed want both, I can do that in the macro I'll write for you but if you want to change the units shown in the Duration field, I can point you to a macro that will do that.
John
Bjorn,
This macro will do what you want. Note, it counts all assignments for each task, including assignments that are non-labor (e.g. material or cost resources). If you have multiple resource types assigned to tasks and you only want a count of labor (i.e. work type) resources,, the macro will need to be modified.

Sub ResCountPlus()
'This macro writes the number of task assignments into Text1
' and writes the number of working days for each task into Text2
Dim t As Task
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
If t.Assignments.Count > 0 Then
t.Text2 = t.Assignments.Count
End If
t.Text1 = t.Duration / 60 / ActiveProject.HoursPerDay & " days"
End If
Next t
End Sub
John
Hi John!
Thank you for that Macro!! It did exactly what i wanted.
Im new to this resource planning so trying it out to see if MS project can replace Excel.
I have not yet found a easy way to place out resources. I do want to stand in the Ghant view and click on a task, get a list of resources that is not assigned to a task yet. So nice if you have a suggestion there. Also, is there an easy way to remove all the decimals in the days column you gave me Macro for?
best response confirmed by Polestar2021 (Copper Contributor)
Solution
Polestar2021,
You're welcome and thanks for the feedback.

What exactly do you mean by, "place out resources"? And I'm confused about you desire to show a list of resources not assigned to a task yet. At the top of the Resource Usage view you can see a list of unassigned resources but, those resources may or may not be appropriate for assignment to any given task.

With respect to removing decimals in the days column, if you simply want a whole number of days then replace the t.Text1 line with this:
t.Text1 = Format(t.Duration / 60 / ActiveProject.HoursPerDay, "##") & " days"

John

@John-project 

 

Thanks again!

If you see the pic i posted here. Im standing in the Ganth view where i have my tasks.

I was hoping i could add recources form here, and i can, but these lists i have found dont show recources

that is free(not added to any tasks.  I have 60 recources, so the list is painfully long.

I know i can go to another view and sort columns so the free recources will be in top.  I was just hoping 

i could stand in Ganth view, find tasks and from there easy get available recources so i can add.

I can see im terrible to explain:facepalm:

Polestar2021,
Using the Assign Resources sub-window as shown in the image you posted is the best way to assign resources while in the Gantt Chart view. Unfortunately, the list of resources is ordered by whether assigned or not. That is, assigned resources start at the beginning of the list and unassigned resources fall to the bottom of the list.

You could also click on the selection arrow in the Resource Names field. You will see all available resources listed with a check in the box next to each that is assigned. This presentation is not sorted so it is a bit easier to see which resources are not assigned. However, checking a resource's box will automatically assign that resource at 100%.

John

@John-project 

Apply the Resource Sheet view.

Add a number field (Number1 for example)

Customize the field and under Calculation for task and group summary rows: select Count all

Then apply a group by on the Group field.

Vinod_Singh_0-1648225607402.png

 

It's very simple just use count formula in resource sheet view under any number field.

@Polestar2021 

Vinod_Singh_0-1648227501532.png

Use a custom field Text and enter the formula in it

[Duration]/[Minutes Per Day] & " days"

and you are done.

Calculation is based on 8 hours per day as defined in project calendar, if you say 7.5 hours, it will change automatically.

Vinod_Singh,
But that's not what the user asked for. The user asked for a count of resources assigned to each task.

The custom field formula you propose will work for converting the Duration units into days, but since VBA is required for the first item (i.e. task resource assignment count), it's easier to roll the conversion into the macro then to have a separate custom field.
John

@John-project 

MS Project is very sufficient tool to manage the project efficiently. No Macro needed.

I used task usage view instead of resource usage and get this. you can slice and dice the way you want to see it. 

In the view "TASK: usage", Group the table after Assignment and then Name (resource-name) + check the box "group aggregation, not tasks" 

 

Vinod_Singh_0-1648245687654.png

 

Vinod_Singh,
Did you read the original post? I don't think you did. The user is showing the Gantt Chart view and is asking for two custom fields in that view. If you've got a way to do that without VBA, I'm listening.
John
Yes I did read John.
There are many different ways to get your job done. He is looking for number of resource count to a task. I not only gave the count but it shows those name of resource count to a given task. You can hide the names and see only counting at summary. There are various views and filters and formulas and settings in MSP.
I have even put a complete risk register that each task you can associate a risk to it and expected delays due to that risk which can give you a original baselined Gantt view and a risk adjusted plan with possible delays.
You can add RAG signals to each tasks that will tell you whether a task is late .
You can filter late tasks, critical tasks etc.
No macro all there just need to be little patient and applying creative thoughts.
This is applicable to project server, where resources are managed at organization level ( program or portfolio) . Where resources are allocated to different tasks and possibly to a different project , then project server will show you availability of all the resources and you can choose which one to assign, of course after approval and it reduces the availability of that resource automatically. In stand alone projects not all resource times dedicated to project related tasks hence best bet will be to update resources sheet and the individual availability then assign them to tasks, look for any over allocation and resolve that by resource levelling.
Vinod_Singh,
Yes you can do a lot of things with Project that aren't obvious to a new or casual user. However, sometimes you just can't get there from here without VBA (e.g. showing data in accounting months, custom reports for all resource exception days, full display of multi-line notes in a Task Sheet type view, etc.)

Your approach indeed shows another way to get a resource count by task. I chose a macro approach because that is the only way to give that count in a Gantt Chart view, as that's what the user requested. Over the many years (20+) I've been helping Project users find solutions, I generally attempt to employ filtering, grouping, sorting, or simple view formatting to address their question/issue but there are times when a simple (or complex) VBA approach is the better, or only answer.

Indeed, Project Server and now Project Online have greater capability in a enterprise environment but many user neither want or need the cost or complexity of those versions.

John
1 best response

Accepted Solutions
best response confirmed by Polestar2021 (Copper Contributor)
Solution
Polestar2021,
You're welcome and thanks for the feedback.

What exactly do you mean by, "place out resources"? And I'm confused about you desire to show a list of resources not assigned to a task yet. At the top of the Resource Usage view you can see a list of unassigned resources but, those resources may or may not be appropriate for assignment to any given task.

With respect to removing decimals in the days column, if you simply want a whole number of days then replace the t.Text1 line with this:
t.Text1 = Format(t.Duration / 60 / ActiveProject.HoursPerDay, "##") & " days"

John

View solution in original post