How to Calculate the Number of Months Between Two Given Dates

Last reviewed: June 16, 1997
Article ID: Q67093

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows
Microsoft Excel for Windows NT, version 5.0

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, "9/7/88").

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("11/2/89"))*12 + Month(Now())-Month("11/2/89")

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("11/2/89"),0,-1)+(YEAR(Now())-YEAR("11/2/89"))
    *12+MONTH(Now())-MONTH("11/2/89")

NOTE: This example assumes Now() refers to October 1, 1990.

Additional query words: 7.00 5.00 2.0 2.00 2.1 2.10 2.2 2.20 3.0 3.00
4.0 4.00
Keywords : xlformula
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 16, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.