To calculate number of months between 2 dates requires =Year and =Month formulas.
Before we get into it, let us first see how to calculate number of days between 2 dates.
To find number of days between 2 dates as super easy!
Simply negate the 2 cells,
- Cell A2 has Start Date
- Cell B2 has End Date
- Simply put the formula in cell C2
=B2 – A2
But if you want to calculate no. of Months between 2 dates, create the below Formula
- Cell A2 has Start Date
- Cell B2 has End Date
- Simply put the formula in cell C2
=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)
Understanding the Fomula
- Year (B2) – Year (A2) * 12
- This will calculate no. of years between the 2 dates and multiply it by 12 to get the no. of months
- So if the 2 dates are falling in the same year, the answer to this part will be 0 * 12 = 0
- Month (B2) – Month (A2)
- This will calculate no. of months between the 2.
- Both values are then added.
Keep Excel-ing!
but it can be done with help of “Datedif()” formula also! m i r8??
Hi Ganesh, yes it can be done. I have written a post on that too.
http://www.excelrush.com/want-to-calculate-age-from-date-of/
The only issue is that DATEDIF is a hidden function, it doesn’t appear directly.
too complicated
Hi David,
I have written some more posts of Date functions. Do go through them. It may help you understand this better and quicker.
http://www.excelrush.com/category/date-time/
Thanks for your comment!
The difference in months can be calculated with the formula —
=datedif(a2,a3,”m”)
Hi Ram, yes it can be done. I have written a post on that too.
http://www.excelrush.com/want-to-calculate-age-from-date-of/
The only issue is that DATEDIF is a hidden function, it doesn’t appear directly.