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

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.

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.

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
;  

Select all 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'
 )

Select all 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
;

Select all 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 |

Select all 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