Article ID: 214134 - View products that this article applies to.
This article was previously published under Q214134
Expand all | Collapse all

On This Page

SUMMARY

This step-by-step article shows you how to calculate the number of months between any two dates.

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 DateNOTE: If you type LDate or EDate directly into this formula instead of into their cell references, you must surround it by quotation marks (for example, "9/7/00").

Round Up

This method does not use the day of the month in its calculations. For example, given a start date of 10/31/00 and an end date of 11/2/00, one month is returned even though only two days elapsed.

For this method, use the following formula
=(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)
as shown in the following example:
  1. Type 10/31/99 in cell A1.
  2. Type the following formula in cell A2:
    =(YEAR(NOW())-YEAR(A1))*12+MONTH(NOW())-MONTH(A1)
    The number that is returned equals the number of months from 10/31/99 to today's date, rounded up to the next whole number.

Round Down

This method uses the number days in its calculations and rounds down to the nearest number of whole months. For example, given a start date of 10/30/00 and an end date of 11/30/00, one month is returned; however, an end date of 11/29/00 returns a zero.

For this method, use the following formula
=IF(DAY(LDate)>=DAY(EDate),0,-1)+(YEAR(LDate)-YEAR(EDate))
*12+MONTH(LDate)-MONTH(EDate)
as shown in the following example:
  1. Type 10/31/99 in cell A1.
  2. Type the following formula in cell B1:
    =IF(DAY(NOW())>=DAY(A1),0,-1)+(YEAR(NOW())-YEAR(A1)) *12+MONTH(NOW())-MONTH(A1)
    NOTE: Ensure that cell B1 is formatted as General. The number returned equals the number of months from 10/31/99 to today's date, rounded down to the nearest number of whole months.

Properties

Article ID: 214134 - Last Review: September 18, 2011 - Revision: 7.0
APPLIES TO
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Office Excel 2003
  • Microsoft Excel 2010
Keywords: 
kbdatetime kbformula kbinfo kbhowtomaster KB214134

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com