Dynamic calculations for capacity

[Jali1992]

I am working with an Excel sheet that tracks project efforts across multiple roles (e.g., Senior Designers, Junior Designers) for each month. The goal is to calculate the total effort needed for each role, factoring in the backlog as well, the calculations should assume that the backlog items would start in on the month of the calculation, for example even though Project 6 can start in Feb-25 I still need to show that in Dec-24 project 6 would still potentially consume 30 days if it were to start in Dec-24. you should note that in Jan-25 I should count the data from Month 2 for a project that started in Dec-24 etc

Here's the link to the file.

https://nishbdorg-my.sharepoint.com...FIrJ40z3xnKuABWdhOsMpSdUrAG14mkJHoFg?e=fwjKe4

I need the calculations to be added to the yellow-highlighted columns from the Monthly Calculations.

As mentioned previously, I have a project (e.g., Project 10) that may start at a later date, such as in April. However, I want the calculation for the required effort in Month 1 of Project 10 to be displayed in December, January, February, and March, since the project hasn't started yet. If it does start later, we would need to account for Month 1's efforts.

Thank you in advance.

 

[Jali1992]

Seems like I can't link the one drive file.

however below is the data.

tab - Project Details

Project Name	Start Date	Duration (Months)	Month 1 Effort (Senior Designers)	Month 2 Effort (Senior Designers)	Month 3 Effort (Senior Designers)	Month 4 Effort (Senior Designers)	Month 5 Effort (Senior Designers)	Month 6 Effort (Senior Designers)	Month 1 Effort (Junior Designers)	Month 2 Effort (Junior Designers)	Month 3 Effort (Junior Designers)	Month 4 Effort (Junior Designers)	Month 5 Effort (Junior Designers)	Month 6 Effort (Junior Designers)
Project 1	Dec-24	5	27	21	37	31	15	0	0	0	0	10	10	0
Project 2	Dec-24	2	32	18	0	0	0	0	0	0	0	0	0	0
Project 3	Dec-24	4	5	5	5	5	0	0	0	0	0	0	0	0
Project 4	Dec-24	4	2	2	2	2	0	0	0	0	0	0	0	0
Project 5	Dec-24	2	3	2	0	0	0	0	0	0	0	0	0	0
Project 6	Feb-25	6	30	30	30	30	30	30	0	0	0	0	0	0
Project 7	Apr-25	1	3	0	0	0	0	0	0	0	0	0	0	0
Project 8	May-25	1	2	0	0	0	0	0	0	0	0	0	0	0
Project 9	Jun-25	2	2	2	0	0	0	0	0	0	0	0	0	0
Project 10	Apr-25	6	25	25	20	15	15	0	0	0	0	15	15	15
Project 11	May-25	4	15	15	10	5	0	0	10	10	10	10	0	0
Project 12	Jun-25	4	15	15	15	5	0	0	10	10	10	10	0	0
Project 13	Apr-25	1	7	0	0	0	0	0	4	0	0	0	0	0

Tab - Monthly Calculations

Month/Year	Senior Designer Capacity	Senior Designer Needed	Expected Results	Remaining Capacity (Senior Designer)	Junior Designer Capacity	Junior Designer Needed	Remaining Capacity (Junior Designer)
Dec-24	68	168	168	-100	0		0
Jan-25	72	141	147	-69	0		0
Feb-25	64	109	143	-45	0		0
Mar-25	64	104	137	-40	0		0
Apr-25	51	102	114	-51	34		34
May-25	51	99	89	-48	34		34
Jun-25	60	69		-9	32		32
Jul-25	68	66		2	36		36
Aug-25	60	64		-4	32		32
Sep-25	64	62		2	34		34
Oct-25	68	37		31	36		36

 

[Jali1992]

FYI - this formula gives me what I need, I would still be open to find out a more optimised approach

=SUMIFS('Project Details'!D:D, 'Project Details'!B:B, ">=" & A2) +
SUMIFS('Project Details'!E:E, 'Project Details'!B:B, "<" & A2, 'Project Details'!B:B, ">=" & EDATE(A2, -1)) +
SUMIFS('Project Details'!F:F, 'Project Details'!B:B, "<" & EDATE(A2, -1), 'Project Details'!B:B, ">=" & EDATE(A2, -2)) +
SUMIFS('Project Details'!G:G, 'Project Details'!B:B, "<" & EDATE(A2, -2), 'Project Details'!B:B, ">=" & EDATE(A2, -3)) +
SUMIFS('Project Details'!H:H, 'Project Details'!B:B, "<" & EDATE(A2, -3), 'Project Details'!B:B, ">=" & EDATE(A2, -4)) +
SUMIFS('Project Details'!I:I, 'Project Details'!B:B, "<" & EDATE(A2, -4), 'Project Details'!B:B, ">=" & EDATE(A2, -5))