Number Of Months Between Any Two Dates

OfficeTips Home || VBA Section || General Section || Download Section || Privacy Policy

Number Of Months Between Any Two Dates

There are no in-built worksheet functions available to calculate the number of months between any two dates. You can use one of the following methods based on your requirements.

Both methods use the following information:

Date format (in the examples) : dd/mm/yy

Ranges: EDate refers to the cell containing the earlier date. LDate refers to the cell containing the later date.

Method 1:This method does not take the days into consideration. Given a start date of 30/1/2000 and an end date of 2/2/2000, 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)

Method 2:This method takes days into consideration. Given a start date of 30/1/2000 and an end date of 2/2/2000, 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)