XL: How to Calculate the Number of Months Between Two Given Dates

ID: Q67093

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
Microsoft Excel for Windows 95, versions 7.0, 7.0a
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: 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


Keywords          : kbdta xlformula 
Version           : WINDOWS:2.x,3.x,4.x,5.0,5.0c,7.0,7.0a,97
Platform          : 
Issue type        : kbhowto

Last Reviewed: March 22, 1999