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.

 

[mgm05267]

Hi GaganR,

Welcome to the Board...

Have you tried DATEDIF Function..!??

Try this...

=DATEDIF(A1,B1,"m")

Hope this is what you are looking for....

 

[mikerickson]

Perhaps
=MONTH(B1)-MONTH(A1)-1+(DAY(A1)/DAY(DATE(YEAR(A1),MONTH(A1)+1,0))) + (DAY(B1)/DAY(DATE(YEAR(B1),MONTH(B1)+1,0)))
If I was more familiar with EOMONTH, I could probably come up with a more compact formula.

 

[AlphaFrog]

This uses the EOMONTH function mikerickson mentioned.

=(DAY(EOMONTH(A1,0))-DAY(A1))/DAY(EOMONTH(A1,0))+

DATEDIF(A1,B1,"m")-1+​

DAY(B1)/DAY(EOMONTH(B1,0))​

 

[mgm05267]

Hi GaganR,

Ignore previous one...

Try this...

=((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)))

 

[Jonmo1]

This is all subjective, there is no right or wrong answer for what "Exactly 1 month" is.
Because of course not all months have the same # of days.

In my opinion, the best answer to "what is an exact month", is 30.4375 Days.

That is 365.25/12

the .25 accounts for leap years.

So try
=(B1-A1+1)/30.4375

A1 is start date, B1 is end date.

 

[Vidar]

Hi

The previous formulas will give #NUM! error if the end date is in the next year.
This one should calciulate correct if the end date is in the next year.
=IF(MONTH(B1)>MONTH(A1),(MONTH(B1)-MONTH(A1)-1),(12-MONTH(A1)+MONTH(B1))-1)+(EOMONTH(A1,0)-A1)/DAY(EOMONTH(A1,0))+DAY(B1)/DAY(EOMONTH(B1,0))

Vidar

 

[GaganR]

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

 

[barry houdini]

=(DAY(EOMONTH(A1,0))-DAY(A1))/DAY(EOMONTH(A1,0))+DATEDIF(A1,B1,"m")-1+DAY(B1)/DAY(EOMONTH(B1,0))

I think this version gives the results you originally asked for, but you can simplify it to this to give the same answer

=DATEDIF(A2,B2,"m")+DAY(B2)/DAY(EOMONTH(B2,0))-DAY(A2)/DAY(EOMONTH(A2,0))

 

[Vidar]

Hi

The previous formulas will give #NUM! error if the end date is in the next year.
Vidar

Sorry, I was wrong there. Forgot to replace a comma with semicolon.