Assigning Numbers to Months

[seguin85]

I have a data table similar to below. What I would like to do it assign the number of machines into one of 3 categories, design, build, commissioning.

If more of the timeline is in the design portion, the # of machines would be assigned to that month. For example, line 10, November to December 2021 would be 4 in design, but January 2022 would have 4 in build as only 7 days in January are in design and the remaining days are in build.

I want the information to be transferred to a table similar to:

Any help would be greatly appreciated!! Been stuck for a while now...

 

[Eric W]

How about:

Book2
	A	B	C	D	E	F	G	H	I	J	K
1				8/1/2021	9/1/2021	10/1/2021	11/1/2021	12/1/2021	1/1/2022	2/1/2022	3/1/2022
2		# Machines	Design	8	8	9	13	4	0	0	0
3		# Machines	Build	0	0	9	9	3	5	5	0
4		# Machines	Comm	0	0	0	0	6	3	0	14
5
6
7
8	# Machines	Design Start	Design Finish	Build Start	Start Ship	Comm Finish
9
10	4	11/3/2021	1/7/2022	5/9/2022	9/1/2022	10/7/2022
11	9	10/15/2021	12/9/2021	2/24/2022	2/25/2022	7/19/2022
12	3	6/9/2021	9/22/2021	10/15/2021	1/15/2022	1/31/2022
13	5	8/9/2021	10/3/2021	1/10/2022	3/10/2022	4/11/2022
14	6	5/15/2021	7/19/2021	9/17/2021	11/21/2021	12/21/2021
15
Sheet3

Cell Formulas
Range	Formula
D2:K2	D2	=SUMPRODUCT($A$10:$A$20,--(IF($C$10:$C$20<EOMONTH(D$1,0),$C$10:$C$20,EOMONTH(D$1,0))-IF($B$10:$B$20>D$1,$B$10:$B$20,D$1)>15))
D3:K3	D3	=SUMPRODUCT($A$10:$A$20,--(IF($E$10:$E$20<EOMONTH(D$1,0),$E$10:$E$20,EOMONTH(D$1,0))-IF($D$10:$D$20>D$1,$D$10:$D$20,D$1)>15))
D4:K4	D4	=SUMPRODUCT($A$10:$A$20,--(IF($F$10:$F$20<EOMONTH(D$1,0),$F$10:$F$20,EOMONTH(D$1,0))-IF($E$10:$E$20>D$1,$E$10:$E$20,D$1)>15))

The design row matches exactly, and the Comm row is pretty close, but the Build row is off by a fair amount from your example. I'm using column D as the Build start date, and column E as the Build end date. Let me know if this works for you.

 

[seguin85]

How about:

Book2
	A	B	C	D	E	F	G	H	I	J	K
1				8/1/2021	9/1/2021	10/1/2021	11/1/2021	12/1/2021	1/1/2022	2/1/2022	3/1/2022
2		# Machines	Design	8	8	9	13	4	0	0	0
3		# Machines	Build	0	0	9	9	3	5	5	0
4		# Machines	Comm	0	0	0	0	6	3	0	14
5
6
7
8	# Machines	Design Start	Design Finish	Build Start	Start Ship	Comm Finish
9
10	4	11/3/2021	1/7/2022	5/9/2022	9/1/2022	10/7/2022
11	9	10/15/2021	12/9/2021	2/24/2022	2/25/2022	7/19/2022
12	3	6/9/2021	9/22/2021	10/15/2021	1/15/2022	1/31/2022
13	5	8/9/2021	10/3/2021	1/10/2022	3/10/2022	4/11/2022
14	6	5/15/2021	7/19/2021	9/17/2021	11/21/2021	12/21/2021
15
Sheet3

Cell Formulas
Range	Formula
D2:K2	D2	=SUMPRODUCT($A$10:$A$20,--(IF($C$10:$C$20<EOMONTH(D$1,0),$C$10:$C$20,EOMONTH(D$1,0))-IF($B$10:$B$20>D$1,$B$10:$B$20,D$1)>15))
D3:K3	D3	=SUMPRODUCT($A$10:$A$20,--(IF($E$10:$E$20<EOMONTH(D$1,0),$E$10:$E$20,EOMONTH(D$1,0))-IF($D$10:$D$20>D$1,$D$10:$D$20,D$1)>15))
D4:K4	D4	=SUMPRODUCT($A$10:$A$20,--(IF($F$10:$F$20<EOMONTH(D$1,0),$F$10:$F$20,EOMONTH(D$1,0))-IF($E$10:$E$20>D$1,$E$10:$E$20,D$1)>15))

The design row matches exactly, and the Comm row is pretty close, but the Build row is off by a fair amount from your example. I'm using column D as the Build start date, and column E as the Build end date. Let me know if this works for you.

How about:

Book2
	A	B	C	D	E	F	G	H	I	J	K
1				8/1/2021	9/1/2021	10/1/2021	11/1/2021	12/1/2021	1/1/2022	2/1/2022	3/1/2022
2		# Machines	Design	8	8	9	13	4	0	0	0
3		# Machines	Build	0	0	9	9	3	5	5	0
4		# Machines	Comm	0	0	0	0	6	3	0	14
5
6
7
8	# Machines	Design Start	Design Finish	Build Start	Start Ship	Comm Finish
9
10	4	11/3/2021	1/7/2022	5/9/2022	9/1/2022	10/7/2022
11	9	10/15/2021	12/9/2021	2/24/2022	2/25/2022	7/19/2022
12	3	6/9/2021	9/22/2021	10/15/2021	1/15/2022	1/31/2022
13	5	8/9/2021	10/3/2021	1/10/2022	3/10/2022	4/11/2022
14	6	5/15/2021	7/19/2021	9/17/2021	11/21/2021	12/21/2021
15
Sheet3

Cell Formulas
Range	Formula
D2:K2	D2	=SUMPRODUCT($A$10:$A$20,--(IF($C$10:$C$20<EOMONTH(D$1,0),$C$10:$C$20,EOMONTH(D$1,0))-IF($B$10:$B$20>D$1,$B$10:$B$20,D$1)>15))
D3:K3	D3	=SUMPRODUCT($A$10:$A$20,--(IF($E$10:$E$20<EOMONTH(D$1,0),$E$10:$E$20,EOMONTH(D$1,0))-IF($D$10:$D$20>D$1,$D$10:$D$20,D$1)>15))
D4:K4	D4	=SUMPRODUCT($A$10:$A$20,--(IF($F$10:$F$20<EOMONTH(D$1,0),$F$10:$F$20,EOMONTH(D$1,0))-IF($E$10:$E$20>D$1,$E$10:$E$20,D$1)>15))

The design row matches exactly, and the Comm row is pretty close, but the Build row is off by a fair amount from your example. I'm using column D as the Build start date, and column E as the Build end date. Let me know if this works for you.

Looks like it works for what I needed! Thank-you Eric!

 

[Eric W]

Happy to help! Thanks for the feedback. ?