Kioskea
Recherche
Ask a question »

Excel - Calculate number of months between 2 dates

March 2015

Microsoft office software has a multi-purpose Excel office software application which is a spreadsheet featuring graphic tools, pivot tables, macro programming and calculations like determining the number of days, months and years between two dates for example. Excel office software provides a number of such functions: you can work out the number of months between two dates in two ways: 1. Dates occurring in the identical year or 2. Dates occurring in two different years. To calculate the number of months for case 1, use the MONTH function to take out the month from every date minus the two months at the start and end and for case 2, use the YEAR and MONTH functions. You can also calculate the date after adding the number of months to another date.

[Excel] Calculate number of months between 2 dates




Issue


I want to work out the number of months between two dates ex: 01/05/2009 as the starting date and 02/27/2010.

Solution


I assume that your dates are set out in this way on your spreadsheet:

   A              B                  
1       Start           End     
2      01/05/09       02/27/10 



Thus you can try this formula in C2:
=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2) 


And if you want to know a date after adding a number of months to another date :
          A              B                  
1       Start         Month number     
2      01/05/09          2     



Try this formula in C2:
=MIN(DATE(YEAR(A2),YEAR(A2)+C2+1,0),DATE(YEAR(A2 ),MONTH(A2)+C2,DAY(A2))) 

Note


Thanks to aquarelle for this tip on the forum.
For unlimited offline reading, you can download this article for free in PDF format:
Excel-calculate-number-of-months-between-2-dates.pdf

See also

In the same category

Published by aakai1056. - Latest update by Virginia Parsons
This document entitled « Excel - Calculate number of months between 2 dates » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.