5 Methods of getting the number of months between two dates:
- Use the YEAR and MONTH function
- Use the MONTH function and subtract the dates
- Use the DATEDIF function
- Use the YEARFRAC and ROUNDUP function
- Use Microsoft VBA code
Let us begin by getting a closer look at these methods.
Method #1: Use the YEAR and MONTH Function
Although this function seems very lengthy, it is ideal if there is a big difference between the two dates, as we subtract both the years and months from each other to ensure the correct results are displayed.
Step #1: Open the Excel workbook
Go to the Excel app and click on Blank workbook to create a new workbook. Enter the required data.
If you already have an existing workbook, open that one.
Step #2: Place the cursor
Click on the cell where you would like to see the results of the formula.
You can confirm the cell by checking the Name box in the top left corner.
In our example, it is the cell next to B2.
Step #3: Enter the formula
The formula to calculate the number of months is:
=(YEAR(end_date) - YEAR(start_date))*12 + MONTH(end_date) - MONTH(start_date) Replace start_date and end_date with the cell names of the start date and end date in your own spreadsheet.
In our example, these are A2 and B2, so our formula would look like this:
=(YEAR(B2) - YEAR(A2))*12 + MONTH(B2) - MONTH(A2) 
Step #4: Press Enter
Once you have finished typing the formula, press Enter.
You can now see the number of months inbetween the two dates.
Method #2: Use the MONTH Function and Subtract the Dates
This method uses the MONTH function to get the month from two dates and subtract one from the other. However, this method has one major drawback, as it doesn’t take years into account. So, you can ONLY use this method if you are calculating months for the same year.
Step #1: Open the Excel workbook
Go to the Excel app and click on Blank workbook to create a new workbook. Enter the required data.
If you already have an existing workbook, open that one.
Step #2: Place the cursor
Click on the cell where you would like to see the results of the formula.
You can confirm the cell by checking the Name box in the top left corner.
In our example, it is the cell next to B3.
Step #3: Enter the formula
Type the Excel formula into the selected cell.
The formula is
=MONTH(end_date) - MONTH(start_date) Replace start_date and end_date with cell names of the start date and end date in your spreadsheet.
Note: Make sure your dates are in the same year.
In our example, the start_date and end_date are A3 and B3 respectively.
Our formula would therefore look like this:
=MONTH(B3) - MONTH(B2) 
Step #4: Press Enter
After entering the formula, press Enter to see the formula in action.
Method #3: Use the DATEDIF Function
This is a simple method that calculates the number of completed months between two years.
Step #1: Open the Excel workbook
Go to the Excel app and click on Blank workbook to create a new workbook. Enter the required data.
If you already have an existing workbook, open that one.
Step #2: Place the cursor
Click on the cell where you would like to see the results of the formula.
You can confirm the cell by checking the Name box in the top left corner.
In our example, it is the cell next to B4.
Step #3: Enter the formula
Type the Excel formula into the selected cell.
The formula is
=DATEDIF(start_date, end_date, “m”) Replace start_date and end_date with cell names of the start date and end date in your spreadsheet.
In our example, the start_date and end_date are A4 and B4 respectively.
Our formula would therefore look like this:
=DATEDIF(A4, B4, "m") 
Step #4: Press Enter
After you enter the function in the selected cell, press Enter.
You will see the calculated number of months.
Method #4: Use the YEARFRAC and ROUNDUP Function
This method uses the YEARFRAC function to get the number of completed years, which is then multiplied by 12 to get the number of months. Because the number of months is given in fractions, we use the ROUNDUP function to get a whole number.
Step #1: Open the Excel workbook
Go to the Excel app and click on Blank workbook to create a new workbook. Enter the required data.
If you already have an existing workbook, open that one.
Step #2: Place the cursor
Click on the cell where you would like to see the results of the formula.
You can confirm the cell by checking the Name box in the top left corner.
In our example, it is the cell next to B5.
Step #3: Enter the formula
Type the Excel formula into the selected cell.
The formula is
=ROUNDUP((YEARFRAC(start_date, end_date)*12),0)Replace start_date and end_date with cell names of the start date and end date in your spreadsheet.
In our example, the start_date and end_date are A5 and B5 respectively.
Our formula would therefore look like this:
=ROUNDUP((YEARFRAC(A5, B5)*12),0)
Step #4: Press Enter
After entering the formula, press Enter to see the number of months.
Method #5: Use Microsoft VBA Code
This is a method that uses Microsoft’s Visual Basic application. It is a very useful method to know and apply to your spreadsheets if you work with dates and calculations every single day.
Step #1: Open your MS Excel workbook
Go to the Excel app and click on Blank workbook to create a new workbook. Enter the required data.
If you already have an existing workbook, open that one.
Step #2: Open Microsoft Visual Basic
Go to the Developer tab and click on the Visual Basic icon to the extreme left.
Step #3: Insert a new module
Once the Microsoft Visual Basic for Applications dialog box is open, click on Insert.
Now, select Module from the drop-down options that appear.
Step #4: Copy and paste the code
This code takes in the date of birth as a parameter and gives the number of completed years. Copy the below code and paste it into the Module 1 dialog box.
Function months(date1 As Date, date2 As Date) As Integer
Dim y1 As Integer
Dim y2 As Integer
Dim d1 As Integer
Dim d2 As Integer
Dim m1 As Integer
Dim m2 As Integer
Dim m_diff As Integer
Dim y_diff As Integer
Dim month_adjustment As Integer
y1 = Year(date1)
y2 = Year(date2)
m1 = Month(date1)
m2 = Month(date2)
d1 = Day(date1)
d2 = Day(date2)
m_diff = m2 - m1
y_diff = (y2 - y1) * 12
If (m_diff > 0 Or y_diff > 0) Then
If (d1 <= 15 And d2 >= 15) Then
month_adjustment = 1
ElseIf (d1 >= 15 And d2 <= 15) Then
month_adjustment = -1
End If
End If
months = m_diff + y_diff + month_adjustment
End Function
Once done, click on the X in the corner to close the window, and exit the Visual Basic window as well.
Step #5: Use the module function
Click on a cell where you want the results to appear.
Enter
=months(start_date, end_date) into the cell. Replace start_date and end_date with cell names of the start date and end date in your spreadsheet.
In our example, that is A6 for the start date, and B6 for the end date.
Press Enter. You can now see the number of months.
To find this for all the cells, just click on the bottom-right corner of a cell and drag it to the desired range of cells.
Conclusion
Hope you found the article useful.