Formula for Getting Months between two Dates

[Hazra Hadee]

I am looking for formula which gives me months which fall in 2019, 2020, 2021, 2022...

For e.g. if my start date is 8-Mar-2019 to 7-Mar-2020, I want the months to be counted for 2019 and 2020 in the separate columns.

Or, if my start date is 1-Dec-2019 and End date is 30-Nov-2020, I want the formula to give me 1 month for 2019 and 11 months for 2020.

I saw the below formula provided by P45Cal in a similar thread and used it:

=IFERROR(DATEDIF(MAX(DATE(C$1,1,1),$A2),MIN($B2,DATE(C$1+1,1,1)),"M"),"") - this one doesn't work for 2020 as for the above example, it gives me 10 months for 2020

=ROUND(YEARFRAC(MAX(DATE(C$1,1,1),$A2),MIN($B2,DATE(C$1+1,1,1)),1)*12,1) - This one works quite a bit except for when the end year is 2019, it still provides value for 2021 and vice versa

I am attaching a file which has both the formulas working. You can see if cel G3, G4 for example, how the input value is showing something when it should be blank.

Thank you for all the help in advance.

 

[pecoflyer]

Is the attached what you are looking for?

 

[Hazra Hadee]

YES! Thank you Pecoflyer! Is it possible to explain the use of the formula and why the other one was not working, that is if possible.

Thanks a million!

 

[pecoflyer]

Datedif with the "m" switch will count entire months between dates
From 20/6 to 01/7 it will return 1 although you cover two months ( not two entire months, mind)
So the formula takes the start date, replaces it with the 1st of the month (using EOMONTH) and the end date is replaced with the 1st of the following month
There is probably a much shorter way, but I am too lazy to search and just built on the existing one

 

[Hazra Hadee]

Datedif with the "m" switch will count entire months between dates
From 20/6 to 01/7 it will return 1 although you cover two months ( not two entire months, mind)
So the formula takes the start date, replaces it with the 1st of the month (using EOMONTH) and the end date is replaced with the 1st of the following month
There is probably a much shorter way, but I am too lazy to search and just built on the existing one

Thank you! Much appreciated.