Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Resource Allocation (personnel) Excel Add-on.

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello,

I am wondering if there is an Excel Add-on (Plugin) which would allow me to do some resource/staff management. I am looking for something that would let me do the following:

1. Able to allocate personnel to several projects. For example 1 person might split half of its time to one project and the other half to a second project.

2. Keep track of time worked by each person on each project

3. Easily scalable to 5-10 projects.

Thanks

View Answers     

Similar Excel Tutorials

Helpful Excel Macros

Delete a VBA Module From Excel
- Delete a VBA macro module from Excel with this macro. This macro allows you to fully remove a macro module from Excel.
Format Cells as Time in Excel
- This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da
Create a Bar Chart With a Macro in Excel
- Create a bar chart in Excel with this macro. You will be able to quickly and easily turn any range of numbers and data
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means

Similar Topics











Hi,

Please help me to identify a staffing template which I need to use for Resource Management.

I am looking for a Resource Management Template to allocate resources between different projects. (IT)

I would like to capture his/her current project allocation, role, skills, experience, usage etc...when a new project comes I would like to know who is available for what?

Thanks,

George



I am not sure if this is the right area but thought it would be a good start. I have been trying to help my management team come up with an equation for our weekly resource allocation spreadsheet.
Background:
Our team builds a weekly resource allocation spreadsheet for the 30+ projects that we are working on. Each manager is responsible for filling in the Estimated time each of their subordinates will be working that week on said items (i.e. project, training, out of office, tickets, O&M, etc.).
Problem:
Each weeks spreadsheet is saved as a different tab on the master spreadsheet. The equation that I have references originally estimate for project time (how much time we think the project will take) minus the previous week(s) total hours used on said project and then subtracts the current weeks total. So the equation looks like this....='8.18.08'!F13-SUM(I27:AG27).... However, as projects move forward and eventually become completed they are removed from the next weeks sheet. Is there a way that I can have all the projects (which have unique key identifiers) reference PROPERLY across the board even though last weeks item may have been in F13 but is now in F12 due to the removal of a project?

Hope that makes sense.




Good day!

I am working on a spreadsheet to monitor project resource availability. While I can show and indicate overlapping projects and availability by DATES, I need some help with representing the % availability.

Attached is a spreadhseet with the "AVAILABILITY" tab indicating overlapping projects and availabiliity by dates.

I was wondering if anyone could help me with putting a total percentage availability into each timeslot on the "AVAILABILITY" tab.

The "PROJECT/RESOURCE" tab is where I enter the % Allocation per resource. I would like the %s to add up per resource and be represented in each DAY SLOT in the "AVAILABILITY" tab. So along with the colour to indicate projects overlapping, I would like to show the % totals per day as well.

For instance, MICHAEL R has 4 parallel projects, 20% per project...i would like it total 80% throughout the "RED" bar. In some cases, it might go over 100% which is ok.

ANY HELP IS APPRECIATED. TOTALLY STUCK.

THANK YOU KINDLY!

Attached is the spreadsheet.

OK we use a 52 sheet excel work book to track our hours against project numbers 08XX for 2008, 09XX for 2009, 10XX for 2010 etc. Each sheet is named by the week for example May-15.

The challenge is that everyone works on different projects and doesn't use a static list of project numbers (since the list grows everytime a client walks in the door) to put their time against but rather inputs the project number in one cell, then the name of the project in the next cell, then the time for each day which is then totaled for the row (week) in another cell at the end all on the same row.

I'm wondering how we can setup a script to automatically see all the 0828 (for example) project lines in every sheet and grab the total time for that week on that project then give me one big master list of time vs every project that person has worked on in one "total's" sheet at the end of the work book. As it stands it takes about half an hour to go through everyone's excel time sheet work books to prepare an invoice.

Idealy this Total's sheet would list any used project numbers in one column, then have a column for each weekly sheet into which it would place the total time against that project for that sheet so we could still track when the hours were being put against the project but it would take a fraction of the time it does now.

I can post an example of our time sheet book if that helps.


Hi there,

I am programming a project portfolio spreadsheet for resource management and my boss wants to programme in start dates for projects.

This means that the resource usage data for that project will start at the corresponding time in the spread sheet.

For example we use quarters, Q1, Q2, Q3, and Q4. I would like to be able to set a project to show its data fron Q3 onward.

Is there any possible way of doing that??

Thanks
sarah




Good day!

Novice user here.

I am trying to create a 'graphical' view of the IT resources and their availability both WEEKLY & MONTHLY. The IT resources consist of 5-6 roles, with each working on multiple projects with different START and END dates. I want to graphical represent the overlapping days to show if they are UNDER or OVER utilized.

Example:

Project Manger working on 3 projects. 40% time allocated to 3 different timelines. When the dates overlap, I would like the % allocation to total and be represented by a colour if they are OVER or UNDER utilized. For instance, the Project Manger with 40% allocation per project, and 3 timelines could potentially have a 120% utilization, I would like that to be highlighted or showed graphically.

What I would like is a table to enter:

HUMAN RESOURCE
PROJECT
START DATE
END DATE
% Allocated

Each resource can be used for multiple projects, with different % allocated per project, but mostly what I would like to accomplish is a VISUAL view of how each is being over and under utilized.

I think I can graphic them each over a timeline, but i would like them to collapse into one with the % totalling for the overlap.

Does this make sense? I'm willing to pay for any help! things are that desperate.

Attached is what i would like the visual to look like. RED indicating close or over 100% utilization, and YELLOW representing the other end of the spectrum.


THANK YOU, THANK YOU, THANK YOU. ANY HELP IS APPRECIATED. YOUNG AND KINDA DUMB.



Hello All

I'm in a new job where Excel is definitely not by comfort zone. However I am now the resident techie and I spend all my spare time reading excel forums and blogs (30 new functions in 30 days - my new life).

Right - I have a problem I cannot work out.

I have a resourcing spreadsheet. 12 columns one for each month of the year. Resources (people) are allocated to projects in rows. One resource can be on multiple projects. The first 5 columns are project, name, grade, etc. Then the months of the year.

Ideally each person should be not have more than 22 man days allocated across all projects - as they then would be at over capacity.

Each person, for a project, how a number of days allocated against a given month. Eg xx project. fred smith, Jan 10 days. Feb 3 days. March 14 days etc. A project can have between 1 and 30 resources working on it.

What I would like to do is use conditional formatting to make the background go red where you enter the number of days planned where the total number of days for that resources exceeds 22. I can easily do formatting where the sum of days is greater than any given number, but I am struggling with linking this for each resource.

It isn't easy to explain so I have uploaded an example of what I am trying to achieve.

I would be REALLY grateful for any pointers. I have got to the point of going around in circles.

Thanks

i have heard of an add in for excel that enables similar functionality as MS Project. Can anyone point me in the right direction? Ultimately, I am trying to add this to a work in process file that will help us plan and allocate human effort resource hours to projects.




I have a number of projects with a time in days allocated. I work on each project, it could be a day/half day/hour at a time. I want to display time allocated in one column, time spent in another and time remaining in another.

Example

Project 1
12 days allocated - 12:00:00 (day,hour, min)
1 Hour spent - 00:01:00 (dhm)
Time remaining - 11:06:30 (dhm)

Working day is 7 and a half hours


I have tried a number of different formulas but cant seem to find one that works. I want to amend the time spent column as I spend time on a project and for it to automatically calculate the time remaining.


Many thanks
Craig

Say I have a table with a list of projects that in column A has the project name ans in B the benefit value and C how many resources. I'd like to be able to return all the projects I can do based on a selected numbe resources available. The look up would first pick off all the higher value projects and as a project is selected reduce the number of available resources before selecting the next project. It has to be able to skip projects tha have a higher value but not enough resources to do the project. So the first criteria is benifit value then availabe resources.


Hi,
I am working on a large chunk of data that is updated every month..for which I intend to use a pivot table.

Objective is simple, get headcount per month. Thing is, resource name appears several times in a month since I also track their percentage allocation. Instead of counting unique instances, the pivot table counts the resource more than once if it is involved in more than one project.

Here is how my table looks like:
Name Project Week Month Allocation Andrew Project A W1 Oct 20% Andrew Project A W2 Oct 30% Andrew Project B W4 Oct 100% Andrew Project B W5 Oct 100% Andrew Project B W1 Nov 100% Ann Project A W1 Oct 80% Ann Project B W2 Nov 90% Ann Project B W3 Dec 100% Lyka Project B W1 Oct 100% Lyka Project B W2 Oct 100% Lyka Project B W3 Oct 100% James Project A W1 Nov 100% James Project A W2 Nov 100% James Project A W3 Nov 100%

Expected result is:
Oct - 3 (andrew, Ann, lyka)
Nov - 3 (james, ann, andrew)
Dec - 1 (ann)

Currently, the pivot table would say Oct - 4 (andrew, adrew, ann, lyka)

What should I do?

Thanks for the help.


I am using Excel to plot resources and their workloads. Each resource has various projects that have different start and end dates. Each project requires a certain amount of hours from that resource. Based on a 40 hour work week I have calculated the average percent per day that the resource will spend on that project. I would like to be able to see on any given day each resource's workload percentage capacity. Seen below this resource is at 99% capactiy from 15/12/08 to 20/12/08. From 05/01/09 to 31/01/09 they are at 100% capacity. From 01/02/09 to 04/02/09 they are at 124% capacity. From 05/02/09 to 15/03/09 they are at 24% capacity.

I have a second worksheet that uses the above information to give me a calendar view of what each resource is working on, but I can not figure out how to show workload capacity.

See Attachment.

Please help Not sure if Excel can do what I am looking for.




I am working on a Financial Model to be used to keep track of a project financials and would appreciate your help.

Design

1) Assumptions - Durations, scope, and categorical descriptors that can be adjusted
2) Resource List - Input the resources that have been assigned on the project. The resource will also have a start and end date.
3) Forecasting - Allocate a percentage for each of the resource defined in 2
4) Populating - The main sheet will then run a based on the allocation of the resource. Actual figures will need to be manually updated once invoices are received. I will use a reference point which will determine if actuals or forecasts will be used in the overall calculation.


Just curious to know your thoughts on how you would approach this.

In tab 2, the resource will have a defined start and end date that I would like to incorporate into the model. That is if the resource starts in jan 2013 and ends feb 2013, then the resource will be inactive.

The issue I have with this, is if we restart the resource in April 2013 with a different daily rate then my vlookup will reference the resource with jan 2013 to feb 2013 and not the one with the new rate.

Hello. In Microsoft Project, I am trying to run a resource usage report from a master project plan comprised of 10 projects. When i use the standard resource usage report the output is name, task, and hours per week over x weeks. I am looking for a report that will show me name, project and hours per week over x weeks. Is there a way to set up a custom report to display project name instead of all the specific tasks within a project?


I've been charged with enhancing the existing staffing chart we use on a weeky basis. Currently, we track multiple projects (30+) and 50+ staff members who often work on more than one. We typically work in 8 hour units (though sometimes we note a 2 or 4 hour block if only limited interaction is required), but we have no way of tallying and individula person's responsibilities without counting up the hours listed next to their name on each of the 4-5 projects they might be working on. We would like to keep our 430 project tracker to a single 11X 17 document, so creating a matrix per person on each project isn't feasible. I'm wondering if there's a way to tally everytime I see Bob's name and his associated hours - would a pull down menu work well for this? Any advice would be greatly appreciated.

i have a pivot table that calculates resource availability. each resource
may work on multiple projects, and each of these projects require a certain
number of hours. the grouping is by person, by project. for each person, i
need to show hours allocated toward each project (detail) and then total
number of hours allocated (subtotal). i also need to show a calculated field
of % hours available (subtotal only) for each person.

John project 1 30 hours (detail)
project 2 15 hours (detail)
Sum of hours 45 hours (subtotal)
Caculated field (1-sum(hours)/40) (subtotal)

how can i show this calculated field only as a subtotal but hide its detail?

thanks much.

--
mt



I am creating a workbook for project managers to manage the projects various teams have active. Once a week we receive a report that is pulled from a CRM (customer resource management) like tool we use to track progress on a day to day basis. This report contains the data for all project managers and I am creating a workbook for each of them to show just their projects at a glance.

Each project manager has a number of teams and each team could have from one to many projects ongoing and active.

What I am trying to do is this:

Lookup the name of the team Determine if there are active projects for that team Return the oldest project completion date for that team Be able to cycle so that when a project is completed the next oldest date is displayed and so on. From the conditional point of view, I have tried using VLOOKUP in a nested IF statement which obviously only returns the first instance in the list based on the conditions. I have looked at the MIN and SMALL functions but can not figure out how they would fit into this formula.

I am stuck and need help to solve this. PLEASE if anyone can help or try to point me in the right direction, it would be greatly appreciated.


Greetings all, having another issue.

First, let me preface this by saying that I'm not very familiar with pivot tables, but I suspect this actually applies to any general formula as well.

The sheet has a series of columns tracking the time a person spends on a particular project. Column A contains the person's name (in no particular order), column B contains the splitcount (more on that later), and column J contains the last date the person will work on a project. This sheet keeps historical project data, so data for projects that have concluded are still retained (therefore, their column J would be less than TODAY())

My question regards the splitcount (column B), which should be 1 divided by the number of active projects if the project on that row is active (TODAY() < column J), and 0 if the project is not active (TODAY() > column J.)

My formula at the moment is (for cell B5):

=IF(OR(H5<>"WEB",AND(J5<>"",TODAY()>J5)),0,1/COUNTIF(A:A,A5))

So far, I have gotten the conditions working to return 0 for an inactive project. The conditional statement before COUNTIF is correct, so it can be ignored.

Currently, the COUNTIF is counting (correctly) the total number of projects that the person in A5 has ever worked on. I need to add a condition that will then check the J column for each project to make sure it is active: OR(J5 = "", TODAY() < J5)

I have tried making the COUNTIF as follows:
COUNTIF(A:A,AND(A5,OR(J:J = "", TODAY() < J:J))

Neither confirming it normally (enter) or as an array (shift+ctrl+enter) has worked so far.

Any ideas?


Hi

I've been trying to find a way to write a VBA program to calculate the optimal solution for allocate projects to a number of different people based on their preferences, but I have had no luck.

For example, I have ten projects that need to be allocated to ten people. Each person states their 1st, 2nd and 3rd preference which are then turned into scores (1st=30, 2nd=20, 3rd=10).

The objective is to allocate the projects to people in such a way that maximises the overall score. So, for example, if everyone was allocated their 1st preference the total score would be 300 (the maximum possible).

What I have been trying to create is a VBA program that could determine the optimal allocation of projects based on preferences, generating the highest total score, which ensured each person was allocated only one project each.

Any ideas would be most welcome!

Thanks
Matt


hello
I am trying to create a workbook with 2 tabs.
The first tab would list what a group of people are working on, and how much of their time is allocated. the same person could show up multiple times throughout the sheet
the second tab would summarize the workload of each person on a given week.
for example
Bob has project A from 6/1 to 6/21 that is 60% of his time
Bob has project B from 6/8 to 6/12 that is 15%
Bob has project C from 6/21 to 6/30 that is 10%

so, my summary sheet would show like this:
Employee: 6/1 6/8 6/15
Bob 60% 75% 70%

since Bob has two projects on the go that fall in the 6/8 range I need it to sum to 75%, then fall to 70% on 6/15 since project B ends on 6/12 but Project A is still scheduled in that time frame.

Any thoughts ??? we use office2003 if it makes a difference.

thanks for any help in advance !




Hello All (and apologies for lenght)

I posted a query on here the other day and it was answered expertly almost straight away - these are the follow up pleas for help!

Original Thread

Hello All

I'm in a new job where Excel is definitely not by comfort zone. However I am now the resident techie and I spend all my spare time reading excel forums and blogs (30 new functions in 30 days - my new life).

Right - I have a problem I cannot work out.

I have a resourcing spreadsheet. 12 columns one for each month of the year. Resources (people) are allocated to projects in rows. One resource can be on multiple projects. The first 5 columns are project, name, grade, etc. Then the months of the year.

Ideally each person should be not have more than 22 man days allocated across all projects - as they then would be at over capacity.

Each person, for a project, how a number of days allocated against a given month. Eg xx project. fred smith, Jan 10 days. Feb 3 days. March 14 days etc. A project can have between 1 and 30 resources working on it.

What I would like to do is use conditional formatting to make the background go red where you enter the number of days planned where the total number of days for that resources exceeds 22. I can easily do formatting where the sum of days is greater than any given number, but I am struggling with linking this for each resource.

It isn't easy to explain so I have uploaded an example of what I am trying to achieve.

I would be REALLY grateful for any pointers. I have got to the point of going around in circles.

Thanks

I had a lovely answer (see attached spreadsheet).

My next bit it in two parts. I know need to make the 'red' conditional on counting in whether the project is Active 'A' and excludes data if the project is on hold 'OH' or Completed 'C'.

I can see it might mean chaning the formula in the condidtional formatting to a sumifs. (I think)

However, (and this is part 2). If anyone could enlighten me as to why the formula appears to be the same in all the cells covered by the condidtional formatting - but it clearly works across different months and different resources. Why oh why? Is there some weird rules about conditional formatting which makes it relative somehow but this isn't reflected in the formula field?

If I could understand it - I can work out how to the multiple conditions I think. But in the mean time if anyone knows how to add the extra condition in that Column A needs to be an 'A' I would be very grateful. An explation would be an added bonus! I am not even sure if this is moon on a stick territory.

Thanks in advance.

OK. I admit I've been out of development for a while. I am attempting to develop a simple resource allocation spreadsheet to help me schedule my team's projects.

I have a listing of projects with the number of hours requested, a start date and the number of hours available to work on projects (among a few other columns irrelevant to this thread). I need a summary of the allocation of each person per week for the next several weeks.

I am fairly close, however, I have a few calculation problems plus one "bonus problem" I would like to resolve.
Code:

Public Function MondayBefore(dt As Date) As Date
  Dim outDate As Date
  ' Sunday rolls forward for free as a result of the Else part
  If Weekday(dt) = 2 Then ' if Monday, stay the same, no math involved
    MondayBefore = dt
  ElseIf Weekday(dt) = 7 Then ' if Saturday, roll forward to Next Mon
    MondayBefore = dt + 2
  Else ' else, not Mon and not Sat, get the prev Mon
    MondayBefore = dt - Weekday(dt) + 2
  End If
End Function

' CurrWeek is the Monday of the week for which we are trying to get the allocation
' ProjResStart is the start date for the project (actually, resource start since 
'       they could be scheduled to start AFTER the project start)
' ProjResEnd is the end date of the project (or resource if they end early)
' ResHrsAvail is the number of hours the resource is available per week
' ProjHrsRemaining is the number of hours remaining in the project (i.e. start
'       the project with 40 hours required, 50% complete = 20 hours remaining)
Public Function GetHrs(CurrWeek As Date, ProjResStart As Date, _
                       ProjResEnd As Date, ResHrsAvail As Double, _
                       ProjHrsRemaining As Double) As Double
  Dim retVal As Double
  
  If (CurrWeek >= MondayBefore(ProjResStart) And _
      CurrWeek <= MondayBefore(ProjResEnd) And _
      CurrWeek <> ProjResEnd) Then
    ' if there are only 4 hours remaining and 8 hours available, return 4
    retVal = LesserOf(ResHrsAvail, ProjHrsRemaining)
  End If

  GetHrs = retVal
End Function


This should be the relevant pieces of code.

The major problem I am having is when the project ENDS on the date passed in or during CurrWeek; it miscalculates.

Another issue is that it seems to be falling down on multi-week spans. If a project spans 4 weeks, the intermediate weeks do not calculate properly. At least that is how it looks in my testing. I have a few weeks in the middle with odd numbers such as 80% utilization (HrsAllocated / HrsAvailable) when I expect 100%.

My bonus piece is the fraction of a project within a week. Say we have a 60 hour project with 40 hours of time available. The current calculation would be 2 weeks at 100% utilization. I would like 1 week at 100% and 1 week at 50%. I am not taking into account the number of remaining project hours.

HELP! How could this function (GetHrs) be rewritten properly? The full workbook is available upon request. I can post it on a web server if requested.

Thanks!


Hi

I've been trying to find a way to write a VBA program to calculate the optimal solution for allocate projects to a number of different people based on their preferences, but I have had no luck.

For example, I have ten projects that need to be allocated to ten people. Each person states their 1st, 2nd and 3rd preference which are then turned into scores (1st=30, 2nd=20, 3rd=10).

The objective is to allocate the projects to people in such a way that maximises the overall score. So, for example, if everyone was allocated their 1st preference the total score would be 300 (the maximum possible).

What I have been trying to create is a VBA program that could determine the optimal allocation of projects based on preferences, generating the highest total score, which ensured each person was allocated only one project each.

Any ideas would be most welcome!

Thanks
Matt


For a small company I would like to have a template which enables me to
weekly easily fill in all (fast changing) projects per person, for the coming
months/year(divided by week). So on the Y-axis I would like to be able to
fill in the persons and the projects. On the X-axis I would like to have all
weeks. Completed with some formulas that sum certain colums and rows.



Hi there -

Here is the context for my question. I have a workbook with 2 worksheets. The first worksheet contains a table with a list of all people in a group. The column headers for that table are skill sets that people might have and the skill level for each resource/person with respect to a skill set populates the table. The second worksheet contains a list of resources that are booked to projects, the projects that they are booked to, and the utilization rate on each project by month. People can be booked to multiple projects at one time, and some people are not listed on the second worksheet because they are currently not booked to a project yet.

Goal: I am currently returning all the projects that a particular resource is working on throughout the course of the year using the formula below

=IF(ISERROR(INDEX('Data Sheet'!$A$6:$B$14,SMALL(IF('Data Sheet'!$A$6:$A$14=$A3,ROW('Data Sheet'!$A$6:$A$14)-MIN(ROW('Data Sheet'!$A$6:$A$14))+1,""),COLUMN(A2)),2)),"",INDEX('Data Sheet'!$A$6:$C$14,SMALL(IF('Data Sheet'!$A$6:$A$14=$A3,ROW('Data Sheet'!$A$6:$A$14)-MIN(ROW('Data Sheet'!$A$6:$A$14))+1,""),COLUMN(A1)),3))

I would like to return all the projects that a particular resource is working on after the cummulative monthly utilization in the first worksheet for a the current month only whatever that might be when the worksheet is retrieved.

I've attached a sample document. Thank you in advance for your help!!