formula to return no. of months in a date range?

Posted by Kevin on December 10, 2001 8:12 AM

Is there a function that will return the number of months from a beginning date to an ending date? For example if cell A1 contains 01/01/2001 and cell A2 contains 02/01/2002, the formula would need to return the value 13.

Thanks,
Kevin

Posted by Dank on December 10, 2001 8:57 AM

How about =(YEAR(A2)-YEAR(A1))*12+(MONTH(A2)-MONTH(A1)) (nt)

Posted by Aladin Akyurek on December 10, 2001 9:03 AM

Also: =DATEDIF(A1,B1,"M"), where B1>A1. [NT]

Posted by Kevin on December 10, 2001 9:38 AM

having slight problem w/ both these formulas...

Aladin,

Thanks for the formulas, however, I see a slight problem in how they work. If my date range is 01/01/01 to 12/31/01, I want the formula to return 12 as the number of months, but instead both formulas return 11 as the number of months. Any way to work around this?

Thanks for your help,
Kevin

Posted by Juan Pablo G. on December 10, 2001 9:43 AM

Re: having slight problem w/ both these formulas...

But the formula IS right... time elapsed is 11 months and 30 days (Not 12 months). 12 months will be in 01/01/02

Juan Pablo G.

Posted by Aladin Akyurek on December 10, 2001 10:05 AM

Re: having slight problem w/ both these formulas...

These date & time things...

Juan is right. It seems you want some rounding here. What about:

=DATEDIF(A3,B3,"M")+ROUND(DATEDIF(A3,B3,"md")/30,0) ?

Aladin

=======

Posted by Kevin on December 10, 2001 11:08 AM

Revised formula works great - thanks!