Microsoft KB Archive/67093

= How to Calculate the Number of Months Between Two Given Dates =

Article ID: 67093

Article Last Modified on 11/16/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q67093





SUMMARY
This article contains information about calculating the number of months between any two given dates.



MORE INFORMATION
To calculate the number of months between any two dates, use one of the following methods. Note that both methods use the following information:


 * Edate = Earlier Date
 * Ldate = Later Date
 * If LDate and/or EDate are entered directly into this formula instead of into their cell references, they must be surrounded by double quotation marks (for example, &quot;9/7/88&quot;).

Method 1
This method does not take days into consideration. For example, given a start date of 10/31 and an end date of 11/2, one month is returned. However, years are taken into consideration. For this method, use the following formula:

  =(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(Edate)

For example, the following sample formula returns 11:

  =(YEAR(NOW)-YEAR(&quot;11/2/89&quot;))*12+MONTH(NOW)-MONTH(&quot;11/2/89&quot;)

NOTE: This example assumes NOW refers to October 1990.

Method 2
This method takes days into consideration. For example, given a start date of 10/31 and an end date of 11/2, 0 (zero) months is returned. Years are also taken into consideration. For this method, use the following formula:

  =IF(DAY(LDate)>=DAY(EDate),0,-1)+(YEAR(LDate)-YEAR(EDate)) *12+MONTH(LDate)-MONTH(EDate)

For example, the following sample formula returns 10:

  =IF(DAY(NOW)>=DAY(&quot;11/2/89&quot;),0,-1)+(YEAR(NOW)-YEAR(&quot;11/2/89&quot;)) *12+MONTH(NOW)-MONTH(&quot;11/2/89&quot;)

NOTE: This example assumes NOW refers to October 1, 1990.

Additional query words: 2.0 2.00 2.1 2.10 2.2 2.20 3.0 3.00 4.0 4.00 XL97 XL7 XL5 XL4 XL3 XL2 XL

Keywords: kbhowto KB67093

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.