How to Get the Number of Months Between Two Dates in MS Excel

You may need to get the number of months between two dates for several reasons. If you are assigned a new project, you may need to know how many months you have to finish it. This Excel trick essentially helps you know the span of any event. There are several ways to get the number of months between two dates in MS Excel.

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.

   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

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

You can easily find the number of months between two dates using any of the above methods, but beware Method #2, as it is only applicable when the year for the start and end dates are the same.

Leave a Comment