counting months

[jpchado]

I need to count the number of months that have passed since a certain date. Is there a way to achieve this in a formula? Ex. From 1/1/01 to 6/1/02 17 months have passed.

 

[lenze]

=DATEDIF(A1,A2,"m")

See Chip Pearson's page on working with dates.

http://www.cpearson.com/excel/datedif.htm
This message was edited by lenze on 2002-09-12 12:44

 

[lenze]

Also, if you need more flexibility, get the Morefunctions add-in available here.

http://longre.free.fr/english/index.html

It has a function DATE.DIFF with more options

 

[New Poster]

On 2002-09-12 12:44, lenze wrote:
=DATEDIF(A1,A2,"m")

See Chip Pearson's page on working with dates.

http://www.cpearson.com/excel/datedif.htm
This message was edited by lenze on 2002-09-12 12:44

Oh?
What about (for example) 31 Dec 2001 and 30 Sep 2002 ?
Your formula returns 8.
Should it not be 9?

 

[lenze]

The Excel version of DATEDIF returns the number of whole months. That's why you get an 8. That's also why the DATE.DIFF may be more flexible.

 

[kskinne]

the following formula:

=IF(MONTH(A1)>=MONTH(A2),MONTH(A2-A1),((YEAR(A2)-YEAR(A1))*12)+MONTH(A2-A1))

will also return the value you want, as well as the value for, say, 12/31/01 and 9/30/02.

or for 1/1/01 to 12/31/01, it will return the value of 12 months instead of 11, as the datedif formula does. depends on what you want i guess.

HTH
kevin

 

[Aladin Akyurek]

On 2002-09-12 13:08, lenze wrote:
The Excel version of DATEDIF returns the number of whole months. That's why you get an 8. That's also why the DATE.DIFF may be more flexible.

I think they behave similarly...

=DATE.DIFF(A1,B1,3)

which gives a result like

0 an 8 mois 30 jours (yes, it's French!)

is much shorter than an equivalent DATEDIF formula.