Calculating Months while taking into consideration days

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Excel Trouble - 08 Feb 2008 17:45 GMT

If I use the suggested formula
"=(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3)"
It will indeed calculate the number of months inbetween two dates, however.

If the date is in question is 2/9/05 and todays current date is 2/8/08
the answer will display as 36 months. That in fact, is incorrect. I want
it to take days into consideration. The correct answer should be 35 months,
and on the 9th of 2008 it will infact be 36 months. How do I make the proper
adjustments???

Reply to this Message

Ron Rosenfeld - 08 Feb 2008 17:50 GMT

>If I use the suggested formula
>"=(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3)"
[quoted text clipped - 5 lines]
>and on the 9th of 2008 it will infact be 36 months. How do I make the proper
>adjustments???

Try

=DATEDIF(A1,A2,"m")

--ron

Reply to this Message

Niek Otten - 08 Feb 2008 17:52 GMT

Look here:

http://www.cpearson.com/excel/datedif.aspx

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| If I use the suggested formula
| "=(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3)"
[quoted text clipped - 5 lines]
| and on the 9th of 2008 it will infact be 36 months. How do I make the proper
| adjustments???

Reply to this Message

Gary''s Student - 08 Feb 2008 17:57 GMT

With A1 and A2 having:

2/9/2005
2/8/2008

=DATEDIF(A1,A2,"m")
returns 35

Signature

Gary''s Student - gsnu200768

> If I use the suggested formula
> "=(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3)"
[quoted text clipped - 5 lines]
> and on the 9th of 2008 it will infact be 36 months. How do I make the proper
> adjustments???

Reply to this Message