You are currently offline, waiting for your internet to reconnect

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

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q214134
For a Microsoft Excel 97 and earlier version of this article, see 67093.

IN THIS TASK

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

To calculate the number of months between any two dates, use one of thefollowing 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:
  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.
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:
  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.
back to the top
XL2007 XL2000 XL2003 XL2002 XL2010
Properties

Article ID: 214134 - Last Review: 09/18/2011 23:01:00 - Revision: 7.0

Microsoft Office Excel 2007, Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition, Microsoft Office Excel 2003, Microsoft Excel 2010

  • kbdatetime kbformula kbinfo kbhowtomaster KB214134
Feedback
ml>/html>avaScript" async=""> var varAutoFirePV = 1; var varClickTracking = 1; var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write("