How many months within 2 date ranges were in specific years/months?

[slam]

Hi everyone!

Quite a spreadsheet project I'm working on today, but I'm just a bit stumped on one little thing.

I'll explain a simple version of it that will probably be enough to get me going.

I have a date range in columns E & F, then in column G I've calculated the number of months within this date range with a DATEDIF. Now the part I'm stuck on is in column H, I need to calculate the number of 2020 Jan through Oct months that occur within this date range, and the same thing in column I for 2021, so the highest value possible for H or I is 10 for the 10 Jan through Oct months.

Here you can see a mockup where I've manually populated the highlighted H and I values to show the results I need to automate.

2021-10-26 Investment Comparison.xlsx
	E	F	G	H	I
1	Start Date	End Date	Total Project Months	2020 Jan through Oct Months	2021 Jan through Oct Months
2	01/01/2021	11/30/2021	10	10	10
3	04/07/2017	03/31/2020	35	3	0
4	12/01/2017	01/29/2021	37	10	1
5	05/01/2017	06/30/2020	37	6	0
6	02/01/2018	02/28/2020	24	0	2
7	04/01/2018	01/31/2020	21	1	0
8	09/27/2020	09/30/2021	12	9	9
Project

Cell Formulas
Range	Formula
G2:G8	G2	=DATEDIF(E2,F2,"m")

Any suggestions? Thanks!

EDIT: Row 8 of my example should be 2 & 9, not 9 & 9. Sorry.

 

[slam]

Sorry, some wrong values in the yellow section above, here's an updated table with correct values (can't edit above post):

2021-10-26 Investment Comparison.xlsx
	E	F	G	H	I
1	Start Date	End Date	Total Project Months	2020 Jan-Oct Months	2021 Jan-Oct Months
2	01/01/2021	11/30/2021	10	0	10
3	04/07/2017	03/31/2020	35	3	0
4	12/01/2017	01/29/2021	37	10	1
5	05/01/2017	06/30/2020	37	6	0
6	02/01/2018	02/28/2020	24	0	2
7	04/01/2018	01/31/2020	21	1	0
8	09/27/2020	09/30/2021	12	2	9
Project

Cell Formulas
Range	Formula
G2:G8	G2	=DATEDIF(E2,F2,"m")

 

[jasonb75]

Your expected results still don't add up correctly, is this what you want?

Book1
	E	F	G	H	I
1	Start Date	End Date	Total Project Months	2020 Jan through Oct Months	2021 Jan through Oct Months
2	01/01/2021	30/11/2021	10	0	9
3	07/04/2017	31/03/2020	35	2	0
4	01/12/2017	29/01/2021	37	9	0
5	01/05/2017	30/06/2020	37	5	0
6	01/02/2018	28/02/2020	24	1	0
7	01/04/2018	31/01/2020	21	0	0
8	27/09/2020	30/09/2021	12	1	8
Sheet19

Cell Formulas
Range	Formula
G2:G8	G2	=DATEDIF(E2,F2,"m")
H2:H8	H2	=DATEDIF(MEDIAN(E2,F2,DATE(2020,1,1)),MEDIAN(E2,F2,DATE(2020,10,31)),"m")
I2:I8	I2	=DATEDIF(MEDIAN(E2,F2,DATE(2021,1,1)),MEDIAN(E2,F2,DATE(2021,10,31)),"m")

 

[Fluff]

How about

=IFERROR(DATEDIF(MAX(E2,DATE(2020,1,1)),MIN(F2,DATE(2020,10,31)),"m")+1,0)

 

[offthelip]

try this :

=IFERROR(MIN(DATEDIF("01/01/2021",$F2,"m"),DATEDIF(E2,$F2,"m")),0)

 

[slam]

Thank you all!

And sorry again, even after my second attempted at a data mockup, I still had one value wrong.

I very quickly tried all 3 formulas. I only got one of them to work perfectly, and for the other two, it very well could be user error:

jasonb75, your formula was giving me 9 months for I2 when it should be 10.

offthelip, yours seemed perfect for 2021, but I couldn't edit it correctly to work for 2020.

Fluff, yours works perfectly for both years with an easy edit for 2021.

Thank you! This forum is amazing!

 

[lrobbo314]

A lot of ways to skin a cat.

Book1
	A	B	C	D	E
1	Start Date	End Date	Total Project Months	2020 Jan through Oct Months	2021 Jan through Oct Months
2	1/1/2021	11/30/2021	10	0	10
3	4/7/2017	3/31/2020	35	3	0
4	12/1/2017	1/29/2021	37	10	1
5	5/1/2017	6/30/2020	37	6	0
6	2/1/2018	2/28/2020	24	2	0
7	4/1/2018	1/31/2020	21	1	0
8	9/27/2020	9/30/2021	12	2	9
Sheet2

Cell Formulas
Range	Formula
C2:C8	C2	=DATEDIF(A2,B2,"m")
D2:D8	D2	=LET(r,SEQUENCE($B2-$A2+1,,$A2),b,r>=DATE(2020,1,1),e,r<=DATE(2020,10,31),n,r/(b*e),COUNT(UNIQUE(MONTH(FILTER(n,NOT(ISERROR(n)))))))
E2:E8	E2	=LET(r,SEQUENCE($B2-$A2+1,,$A2),b,r>=DATE(2021,1,1),e,r<=DATE(2021,10,31),n,r/(b*e),COUNT(UNIQUE(MONTH(FILTER(n,NOT(ISERROR(n)))))))

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Fluff]

A lot of ways to skin a cat.

True, but some are simpler than others.

 

[jasonb75]

jasonb75, your formula was giving me 9 months for I2 when it should be 10.

If I2 should be 10 then surely G2 should be 11