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]

If I2 should be 10 then surely G2 should be 11

Hmm, that's a good point. DATEDIF is rounding down by default? Or not counting a month unless its a full month? Can I make it round up somehow?

 

[jasonb75]

It depends at what point it should round up. I haven't checked it in detail but from past observations, I think that the day in the end month has to be equal to or greater than the day in the start month in order for that month to be counted.