Microsoft KB Archive/214134

= How to calculate the number of months between two dates in Excel =

Article ID: 214134

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214134





For a Microsoft Excel 97 and earlier version of this article, see 67093.

IN THIS TASK
SUMMARY
 * Round Up
 * Round Down



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").

back to the top

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:  Type 10/31/99 in cell A1. 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.

back to the top

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:  Type 10/31/99 in cell A1. 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.

back to the top

Additional query words: XL2007 XL2000 XL2003 XL2002

Keywords: kbdatetime kbformula kbinfo kbhowtomaster KB214134

-

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

© Microsoft Corporation. All rights reserved.