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:
| 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
