Calculate Project Allocations

[slee0124]

I have a dataset with a person name, project name, allocation % for that project (say 25%) and start and end dates (say 6/1/22 to 9/30/22). So I know this person is allocated 25% across the duration of this project for 4 months, which tells me this person has only availability of 75% during this 4 month period. They may also have other projects that they are working on that start or finish before or after this project, but there may be overlap. So I'm trying to figure out with only having start and end dates, how in a pivot table and chart ultimately to show for any given month, how much a person is allocated (ie. are they available or not?) based on 1 or more projects they are working on...Essentially could be done with MS project, but I'm pulling this data from a SharePoint list. Here's what this looks like in the top table and what I'm trying to work toward with formulas...

Resource

Allocation %

Project Phase

Priority

Tower

Status

BU

% Complete

Project Name Full

Start Date

Due Date

Person Name A

0.25​

Build

High

Microsoft

In Progress

Enterprise

100%​

Test Project #1

6/1/2022​

9/30/2022​

Person Name B

0.15​

Build

High

Microsoft

In Progress

Enterprise

100%​

Test Project #1

4/1/2022​

10/31/2022​

Person Name A

0.4​

Build

High

Microsoft

In Progress

Enterprise

100%​

Test Project #1

1/1/2022​

11/30/2022​

Person Name B

0.5​

Build

High

Microsoft

In Progress

Enterprise

100%​

Test Project #1

5/1/2022​

8/30/2022​

Person Name C

0.35​

Build

High

Microsoft

In Progress

Enterprise

100%​

Test Project #1

3/1/2022​

12/31/2022​

% Allocated by Month

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Person Name A

0​

0​

0​

0​

0​

25​

25​

25​

25​

0​

0​

0​

Person Name B

0​

0​

0​

15​

15​

15​

15​

15​

15​

15​

0​

0​

Person Name A

40​

40​

40​

40​

40​

40​

40​

40​

40​

40​

40​

0​

Person Name B

0​

0​

0​

0​

50​

50​

50​

50​

0​

0​

0​

0​

Person Name C

0​

0​

35​

35​

35​

35​

35​

35​

35​

35​

35​

35​

Final Allocation % Totals

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Person A

40​

40​

40​

40​

40​

65​

65​

65​

65​

40​

40​

0​

Person B

0​

0​

0​

15​

65​

65​

65​

65​

15​

15​

0​

0​

Person C

0​

0​

35​

35​

35​

35​

35​

35​

35​

35​

35​

35​

Ultimately, I would like to do this in a pivot table, but can't figure out how to fill in the missing months with the values based on the dates.

Any genius' out there that can help please?

Thanks,
Sean

 

[slee0124]

Sorry, just realized I didn't update the projects in the table...Each of those line items in the top table are different projects, so should be Project #1, Project #2, Project #3 and so on. Thanks!