How to Calculate Exact No. of Months between Two Dates

[GaganR]

How Could I calculate Exact Number of Months between Two Days. For Example
IF in A1= 06/11/2012
and B1= 08/17/2012
The Exact Number of Months between them comes to 2.1817 i.e.
June : 19 days / 30 days = 0.6333
July : 31 days / 31 days = 1
Aug : 17 days / 31 days = 0.5484
0.6333 + 1 + 0.5484 = 2.1817

Now how to calculate this in excel using a formula for all subsequent dates I enter.

 

[Rick Rothstein]

Thanks!!!!!!!! the Formula worked really Fine.

I'm curious as to what use you are planning on putting this calcuation to. I mean, what use is there for a number that varies depending on the starting date (especially given you showed an answer out to 4 decimal place in your original message)? For example, consider these two spans of time both of which contain the same number of days...

December 1, 2011 to February 29, 2012 --- 2.9677 months

January 1, 2012 to March 29, 2012 --- 2.9032 months

 

[Jonmo1]

I'm curious as to what use you are planning on putting this calcuation to. I mean, what use is there for a number that varies depending on the starting date (especially given you showed an answer out to 4 decimal place in your original message)? For example, consider these two spans of time both of which contain the same number of days...

December 1, 2011 to February 29, 2012 --- 2.9677 months

January 1, 2012 to March 29, 2012 --- 2.9032 months

Agreed, hence my suggestion of using a standardized value for "Exactly 1 month"
So that results are consistent accross all possible start/end date combinations.
365.25/12

 

[Rick Rothstein]

Agreed, hence my suggestion of using a standardized value for "Exactly 1 month"
So that results are consistent accross all possible start/end date combinations.
365.25/12

I saw your posting, but I am not sure I like that as a solution either... although I guess it might depend on what ultimate use the OP has for this number. Personally, I think any use that involves measuring in "exact" months (especially if down to 4 decimal places) will ultimately be flawed in some way. A question for the OP: What could possibly be wrong with simply using days as your "yardstick"?

 

[Jonmo1]

I guess the real question is....

Which formula worked?

Thanks!!!!!!!! the Formula worked really Fine.

 

[GaganR]

I guess the real question is....

Which formula worked?

The formula given by MGM05267
=((DAY((DATE(YEAR(A1),1+MONTH(A1),1)-1))-(DAY(A1)))/(DAY((DATE(YEAR(A1),1+MONTH(A1),1)-1))))+(DATEDIF(A1,B1,"m")-1)+((DAY(B1))/(DAY(DATE(YEAR(B1),1+MONTH(B1),1)-1)))

 

[barry houdini]

You might want to take on board Jonmo and Rick's observations about the validity of this method......but if that's the result you want then you could get the same thing using the formula I suggested, or if you want to avoid EOMONTH then this version would also do the same

=DATEDIF(A1,B1,"m")+DAY(B1)/(32-DAY(B1-DAY(B1)+32))-DAY(A1)/(32-DAY(A1-DAY(A1)+32))

 

[tiger3p]

You might want to take on board Jonmo and Rick's observations about the validity of this method......but if that's the result you want then you could get the same thing using the formula I suggested, or if you want to avoid EOMONTH then this version would also do the same

=DATEDIF(A1,B1,"m")+DAY(B1)/(32-DAY(B1-DAY(B1)+32))-DAY(A1)/(32-DAY(A1-DAY(A1)+32))

Hello,

Used the formula above but the results I'm getting is not what I'm expecting. For example:

Cell A2: 3/30/2018
Cell B2: 3/1/2019

The number of months missing should be 12, but I'm getting 10.06.

Cell A3: 12/10/2018
Cell B3: 3/1/2019

The number of months missing should be 2 months but the formula is giving me 1. Anyway to get around this?

Thanks in advance!