Avatar of Conor Newman
Conor NewmanFlag for Ireland

asked on 

Trying to build a formula in a table to cross reference multiple data fields (Work out Loading of resources across multiple projects by month)

This one is probably a lot simpler than I think, and I'm guessing I've massively over complicated everything.
Basically I have a workbook of tables of data. The tables record details of staff, their monthly working hours, current projects, what staff are assigned to those and how many hours they have a month on each project, what percentage of their total availability that is etc

Tables: Projects: This is where I list all the projects, and who is assigned, and how many hours
Tables: Resource: This is the list of staff, and a divider to get their weekly hours (From 40 max, so set to 2 would mean their weekly hours are 20)
Tables: WorkHours: This calculates using network days how many working hours there are in a given month, and reduces that by bankholidays etc
Tables: MonthsActive: This calculates what months each project is live for
Tables: ResourceLoading: Should calculate total hours Loading for each member of staff for each month

Resource loading is where my issue is. I'm stuck trying to build any kind of formula to do this.  Last resort, I'll go VBA, but if this is possible to do in the worksheet with Formulae, I'd rather go that route.

Workbook attached.
staff-loading-look-ahead---declassi.xlsm
Microsoft ExcelMicrosoft OfficeProject ManagementOffice ProductivityVBA

Avatar of undefined
Last Comment
Conor Newman
ASKER CERTIFIED SOLUTION
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
I certainly don't know what I'm talking about but I thought I'd speak up since I found a link.

This site has several conceptual methods for you to consider.  Please correct me if I'm wrong, but this is a large undertaking and the question covers a lot of ground.  I am sending this link to give you ideas.  Maybe it will help you get started and assist with generating specific questions, Maybe it will help you more easily see ways to do what you want to do.  Then again, with all your excel experience, maybe this is old info.

I started out thinking, I'll ask what type of analysis is needed and so forth, but you may change your answers after reading the link.  It covers functions like DAVERAGE, DCOUNT, DCOUNTA, creating histograms, and Progress S-curves.  I wonder what you think of it.

http://www.toolboxforplanning.com/search/label/Excel%20Tutorial

By the time you review it, maybe someone who does a lot of this will be responding to you here.
Avatar of PortletPaul
PortletPaul
Flag of Australia image

I wouldn't dream of doing this in Excel

(I'm biased however, I have used commercial off-the-shelf PM systems)

I would capture the necessary data into a database (e.g. Access, SQL Server, MySQL, Oracle)

The data tables designed to "third normal form"
(basically: so you only have to enter new data once)

Then I would use SQL for the logic required to produce the information sought.

Spreadsheets are tremendous at so many things, but they do not replace databases for things of this nature in my view.

other options:

Look for an existing off-the-shelf solution, many exist. Often these are available cheaply as cloud based systems. There might be open source solutions.
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Here are 3 sample tables and data (Projects, Reources, and ProjectResources). In the ProjectResources table I have introduced an Allocation Start/End and an FTE. The assmption being that a person might be on a project for less than the project duration, and during that time they may be full time to the project, or part-time to the project. With this table you could also have the same person full time for (say) a month, then 20% allocated for 3 months etc.

In addition to those tables I have partially built a "calendar table" based on an article by Jim Horn this table is an essential tool in helping us with "working days" and how many hours are allocated into the future.
CREATE TABLE Projects
    (
     id int identity primary key, 
      [ProjectName] varchar(14), [StartDate] datetime, [FinishDate] datetime)
;
    
INSERT INTO Projects
    ([ProjectName], [StartDate], [FinishDate])
VALUES
    ('Test Project 1', '2015-10-01 00:00:00', '2015-12-31 00:00:00'),
    ('Test Project 2', '2015-09-01 00:00:00', '2015-12-31 00:00:00'),
    ('Test Project 3', '2015-09-01 00:00:00', '2015-12-31 00:00:00'),
    ('Test Project 4', '2016-01-01 00:00:00', '2016-03-31 00:00:00')
;

CREATE TABLE Resources
    (
     id int identity primary key, 
      [Resource] varchar(7), [WeeklyHours] decimal(4,2))
;
    
INSERT INTO Resources
    ([Resource], [WeeklyHours])
VALUES
    ('Staff 1', 1.07),
    ('Staff 4', 1.07),
    ('Staff 2', 2.00),
    ('Staff 3', 1.07)
;

CREATE TABLE ProjectResources
    ([ProjectID] int, [ResourceID] int, [FTE] decimal(4,2), [AllocStart] date, [AllocEnd] Date)
;
    
INSERT INTO ProjectResources
    ([ProjectID], [ResourceID], [FTE])
VALUES
    (1, 1, 1.0),
    (1, 2, 0.5),
    (2, 1, 1),
    (2, 2, 1),
    (2, 3, 1),
    (3, 1, 1),
    (3, 3, 1),
    (3, 4, 1),
    (4, 1, 0.5),
    (4, 2, 0.5)
;

CREATE TABLE days (
   PKDate date NOT NULL PRIMARY KEY CLUSTERED, 
   -- Years
   calendar_year smallint,
   -- Quarters 
   calendar_quarter tinyint, 
   calendar_quarter_desc varchar(10), 
   -- Months
   calendar_month tinyint, 
   calendar_month_name_long varchar(30), 
   calendar_month_name_short varchar(10), 
   -- Weeks
   calendar_week_in_year tinyint, 
   calendar_week_in_month tinyint,  
   -- Days
   calendar_day_in_year smallint, 
   calendar_day_in_week tinyint,  -- The first of the month 
   calendar_day_in_month tinyint, 
   mdy_name_long varchar(30), 
   mdy_name_long_with_suffix varchar(30), 
   day_name_long varchar(10), 
   day_name_short varchar(10), 
   -- Continuous Y/M/D, starts with the first day = 1 and keeps going.  Used for various dateadd functions.
   continuous_year tinyint,
   continuous_quarter smallint, 
   continuous_month smallint, 
   continuous_week smallint, 
   continuous_day int, 
   -- Custom
   description varchar(100), 
   is_weekend tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_holiday tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_workday tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_event tinyint)        -- Used to indicate any special event days. 
GO

-- Create the table, with dates ranging from 2010 to 2020.  Change to suit your needs.
Declare @dt_start date = '2015-01-01', @dt_end date = '2020-12-31', @total_days int, @i int = 0
SELECT @total_days = DATEDIFF(d, @dt_start, @dt_end) 

WHILE @i <= @total_days
   begin
   INSERT INTO days (PKDate) 
   SELECT CAST(DATEADD(d, @i, @dt_start) as DATE) 

   SET @i = @i + 1
   end
   
 ;
 
 -- These values can be generated with single SQL Server functions
UPDATE days
SET 
   calendar_year = YEAR(PKDate), 
   calendar_quarter = DATEPART(q, PKDate),
   calendar_month = DATEPART(m, PKDate), 
   calendar_week_in_year = DATEPART(WK, PKDate), 
   calendar_day_in_year = DATEPART(dy, PKDate), 
   calendar_day_in_week = DATEPART(Weekday, PKDate),
   calendar_day_in_month = DATEPART(d, PKDate),
   day_name_long = datename(weekday, PKDate)

-- These values need either logic, customization in functions, or customization based on client needs.
UPDATE days
SET 
   is_weekend = CASE DATEPART(weekday, PKDate) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END, 
   calendar_quarter_desc =  'Q' + CAST(calendar_quarter as char(1)) + ' ' + CAST(calendar_year as char(4)), 
   calendar_month_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_year as CHAR(4)),
   mdy_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + ', '  + CAST(calendar_year as CHAR(4)),
   day_name_short = LEFT(datename(weekday, PKDate),3)
   
-- Month name:  The first three letters of the month.
UPDATE days
SET calendar_month_name_short = LEFT(DATENAME(month, PKDate),3), is_holiday = 0
;  

Open in new window

record some dates as holidays which will be removed (along with weekends) from allocaton calculations
-- record "bank holidays" these are some UK dates from http://www.telegraph.co.uk/history/11622952/bank-holiday-facts.html
UPDATE days
SET is_holiday = 1
where PKDate IN (
  '20150101','20150403','20150406','20150504','20150525','20150831','20151225','20151228'
 ,'20160101','20160325','20150328','20150502','20150530','20150829','20151226','20151227'
 )

Open in new window

With the 4 tables and data, we can combine these to calculate forward load, which I have done per project, per resource , per month (& it could be done per week or quarter for example). The query I used:
select
     p.id
   , p.ProjectName
   , r.Resource
   , pr.fte
   , d.calendar_year
   , d.calendar_month
   , format(max(coalesce(pr.AllocStart, p.StartDate)),'yyyy-MM-dd') AllocFrom
   , format(max(coalesce(pr.AllocEnd, p.FinishDate)),'yyyy-MM-dd') AllocTo
   , sum(8 * pr.FTE) AllocHrs
from ProjectResources pr
inner join Projects p on pr.projectid = p.id
inner join Resources r on pr.resourceid= r.id
inner join dbo.days d on d.pkdate between coalesce(pr.AllocStart, p.StartDate) and coalesce(pr.AllocEnd, p.FinishDate)
                     and d.is_weekend = 0 and d.is_holiday = 0
group by
     p.id
   , p.ProjectName
   , r.Resource
   , pr.fte
   , d.calendar_year
   , d.calendar_month
order by 
     p.id
   , p.ProjectName
   , r.Resource
   , pr.fte
   , d.calendar_year
   , d.calendar_month
;

Open in new window

and the result of that query for the sample data is:
| id |    ProjectName | Resource | fte | calendar_year | calendar_month | AllocHrs |
|----|----------------|----------|-----|---------------|----------------|----------|
|  1 | Test Project 1 |  Staff 1 |   1 |          2015 |             10 |      176 |
|  1 | Test Project 1 |  Staff 1 |   1 |          2015 |             11 |      168 |
|  1 | Test Project 1 |  Staff 1 |   1 |          2015 |             12 |      168 |
|  1 | Test Project 1 |  Staff 4 | 0.5 |          2015 |             10 |       88 |
|  1 | Test Project 1 |  Staff 4 | 0.5 |          2015 |             11 |       84 |
|  1 | Test Project 1 |  Staff 4 | 0.5 |          2015 |             12 |       84 |
|  2 | Test Project 2 |  Staff 1 |   1 |          2015 |              9 |      176 |
|  2 | Test Project 2 |  Staff 1 |   1 |          2015 |             10 |      176 |
|  2 | Test Project 2 |  Staff 1 |   1 |          2015 |             11 |      168 |
|  2 | Test Project 2 |  Staff 1 |   1 |          2015 |             12 |      168 |
|  2 | Test Project 2 |  Staff 2 |   1 |          2015 |              9 |      176 |
|  2 | Test Project 2 |  Staff 2 |   1 |          2015 |             10 |      176 |
|  2 | Test Project 2 |  Staff 2 |   1 |          2015 |             11 |      168 |
|  2 | Test Project 2 |  Staff 2 |   1 |          2015 |             12 |      168 |
|  2 | Test Project 2 |  Staff 4 |   1 |          2015 |              9 |      176 |
|  2 | Test Project 2 |  Staff 4 |   1 |          2015 |             10 |      176 |
|  2 | Test Project 2 |  Staff 4 |   1 |          2015 |             11 |      168 |
|  2 | Test Project 2 |  Staff 4 |   1 |          2015 |             12 |      168 |
|  3 | Test Project 3 |  Staff 1 |   1 |          2015 |              9 |      176 |
|  3 | Test Project 3 |  Staff 1 |   1 |          2015 |             10 |      176 |
|  3 | Test Project 3 |  Staff 1 |   1 |          2015 |             11 |      168 |
|  3 | Test Project 3 |  Staff 1 |   1 |          2015 |             12 |      168 |
|  3 | Test Project 3 |  Staff 2 |   1 |          2015 |              9 |      176 |
|  3 | Test Project 3 |  Staff 2 |   1 |          2015 |             10 |      176 |
|  3 | Test Project 3 |  Staff 2 |   1 |          2015 |             11 |      168 |
|  3 | Test Project 3 |  Staff 2 |   1 |          2015 |             12 |      168 |
|  3 | Test Project 3 |  Staff 3 |   1 |          2015 |              9 |      176 |
|  3 | Test Project 3 |  Staff 3 |   1 |          2015 |             10 |      176 |
|  3 | Test Project 3 |  Staff 3 |   1 |          2015 |             11 |      168 |
|  3 | Test Project 3 |  Staff 3 |   1 |          2015 |             12 |      168 |
|  4 | Test Project 4 |  Staff 1 | 0.5 |          2016 |              1 |       80 |
|  4 | Test Project 4 |  Staff 1 | 0.5 |          2016 |              2 |       84 |
|  4 | Test Project 4 |  Staff 1 | 0.5 |          2016 |              3 |       88 |
|  4 | Test Project 4 |  Staff 4 | 0.5 |          2016 |              1 |       80 |
|  4 | Test Project 4 |  Staff 4 | 0.5 |          2016 |              2 |       84 |
|  4 | Test Project 4 |  Staff 4 | 0.5 |          2016 |              3 |       88 |

Open in new window

& now that I have used the dbms to generate the numbers I could use a spreadsheet to transpose the data into monthly columns instead of rows for presentation and probably some graphs.

Working demonstration: http://sqlfiddle.com/#!6/54345/4
Avatar of Conor Newman
Conor Newman
Flag of Ireland image

ASKER

Exactly what I was looking for! Thank you.
Avatar of Conor Newman
Conor Newman
Flag of Ireland image

ASKER

@Paul Maxwell, Definitely a database is the ultimate goal, but I have to generate a report from this for Monday, so need to do it manually in Excel right now, but your solution above is excellent, and would have been a nother question in a few weeks. Thank you.
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo