How to Calculate the Number of Months Between Two Given Dates (67093)

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.