If working with dates in Excel you may need to find out how many months are between two dates. Fortunately, Excel provides some excellent Date and Time functions that allow us to calculate such differences.
Difference Between Two Dates in Months
To find the difference between two dates in months we can use the MONTH function in Excel. The formula below finds the number of months between the date in cell A1 and the date in cell B1.
=MONTH(A1)-MONTH(B1)
The MONTH function extracts the number of the month in the year. So for example, with the 12/08/2011, the MONTH function would return 8.
This formula subtracts the month returned from one date from the month returned from the other date. It is a basic calculation that does not take into account dates that span multiple years.
Difference when the Dates Span Multiple Years
If the two date involved in the calculation span multiple years then the formula needs to be adapted. The formula below can be used to find the difference between two dates, that span multiple years, in months.
=IF(DAY(B1)>DAY(A1),0,-1)+(YEAR(A1)-YEAR(B1))*12+MONTH(A1)-MONTH(B1)
This formula can be broken into two sections:
The second section includes the (YEAR(A1)-YEAR(B1))*12+MONTH(A1)-MONTH(B1).
The YEAR function is used in the same way as the MONTH function. It finds the difference between the two years and multiplies it by 12 to get the answer in months. The difference in months is then added to this total.
This is fine, however it does not taken into account the day of the month. For example, this formula would produce the answer of 1 months difference between the dates of 30/12/2010 and the 02/01/2011. When really there is only 3 days difference.
The first section of the formula; IF(DAY(B1)>DAY(A1),0,-1)+ is used to check if the day of the past date is greater than the day of the more recent date. If it is then the number of months is subtracted by 1, and if it is not then there is no change.
Format the Difference Showing Years and Months
This last example demonstrates the CONCATENATE function being used to create a text that shows the number of years and months between two dates. The results would show 1 years 3 months.
=CONCATENATE(YEAR(B1)-YEAR(A1)," years ",IF(MONTH(A1)>MONTH(B1),0,MONTH(B1)-MONTH(A1))," months ")
The YEAR and MONTH functions are used again to find the difference. The IF function is used to check if the number of months of the older date is greater than the number of months in the more recent date. If it is then the number of months is 0, and if not it needs to be calculated.
Leave a Reply