Formula to calculate the number of months between two dates

[mysticmoron109]

Good morning,

Ive done some searching but the formulas i have found are not working.

What I need is a formula which will calculate the number of months between two dates, INCLUSIVE of the current month.

For example:

January 8, 2015 - January 28 2015 = 1 month.
January 8, 2015 - May 20, 2015 = 5 months

and so on.

Thank you!!

[zbor]

How abouth:

January 8, 2015 - May 2, 2015 ?

[samba_ravi]

January 8, 2015 - May 20, 2015 = 6 months
how / why it is 6 Months

[mysticmoron109]

please excuse me that was a typo. I meant 5 months.

and it needs to return a whole number for the difference in months, inclusive of the current month.

[Glenn Kennedy]

Onevway.. (of many...)

=DATEDIF(EOMONTH(A1,-1)+1,EOMONTH(B1,0),"M")+1

[JesMath]

try:
=Month(B1)-Month(A1)+1

[Steve7913]

if they are all with in the same year then this will work...

=(MONTH((F26))-MONTH((G26)))+1

however, once your start changing years, you will need something a little more complex..


=DATEDIF(C11,D11,"M")

[SDCh]

Simple way:

Please Login or Register  to view this content.

Note : date at a1 < date at b1

For information about datedif:
http://www.exceltrick.com/formulas_m...edif-function/
http://www.cpearson.com/excel/datedif.aspx

[mysticmoron109]

some data entries span 2 years.

I am using Excel 2010 and DATEDIF returns a #NAME error.

[mysticmoron109]

if they are all with in the same year then this will work...

=(MONTH((F26))-MONTH((G26)))+1

however, once your start changing years, you will need something a little more complex..

THis is what I am using now, but I take your point once we cover 2 years, which is what happens in my data. Who would have thought something so simple would be so complex to write a formula for? gah

[JesMath]

=(year(k81)-year(j81))*12+(month(k81)-month(j81))+1

[SDCh]

Datedif return #NAME ?

I always use this formula for span more than 5 years, and nothing happen like what you said

[Steve7913]

I modified my post to show the "DateDif" formula.. it should do the trick

[JesMath]

@SDCh:
your suggested formula =DATEDIF(...) works as well

[samba_ravi]

or
=DATEDIF(,B1,"m")-DATEDIF(,A1,"m")+1

Please Login or Register  to view this content.

[mysticmoron109]

Excellent. That was a bit tricky but ended up working out. I got two options form this post.

Thanks a lot folks!