Sometimes you may want to find out the number of months between two dates.

For example, if you are a project manager, you may want to find out how many months a certain project would take to complete when you know the start and the end date.

In this tutorial, you will learn five methods of calculating the number of months between two dates in Excel.

**Note:** Microsoft Excel stores date values as sequential serial numbers. 1 January 1900 is stored as the serial number 1, 15 January 1900 is serial number 15, and so on. This is what makes it possible for us to use dates in calculations.

## Method #1: DATEDIF function to Calculate Months Between Two Dates

The easiest way to calculate the total number of months between two dates is by using the **DATEDIF function** (called DATEDIF as it calculated the date difference).

Below I have a data set where I have the start date in column B and the end date in column C, and I want to calculate the number of months between these two dates in column D:

Below are the steps to get the number of months between the start and the end date:

- Select cell
**D2**and type in the following formula:

=DATEDIF(B2,C2,”m”)

- Press the Enter key to get the result in cell D2. You can then copy the formula in all the remaining cells (you can do a simple copy-paste or you can use the fill handle and drag it down to copy the formula down the column).

Once done, you will have the number of months between the two dates in column D

### Explanation of the formula

=DATEDIF(B2,C2,”m”)

The formula uses the **DATEDIF** function, which uses an identifier as the last argument and can calculate the number of days, months, or years between two given dates.

This function is a legacy function retained in Excel for the purpose of compatibility with Lotus 1-2-3. Therefore, it does not show up as part of IntelliSense and it **must be entered manually**.

The syntax of the **DATEDIF** function:

DATEDIF(start_date,end_date,unit)

- The
**start_date**is a required argument and represents the starting date of a given period. In our example, it represents the start date of the project. - The
**end_date**is a required argument and represents the last date of the period. In our example, it represents the last date of the project. - The
**unit**argument is required, and it represents the type of information you want to be returned. In our example, we wanted months and therefore used the**“m”**unit. You can use “y” for calculating years and “d” for calculating days

**Note:** This function only calculates completed months.

In case you want to get the number of months between a start date and the current date (today), use TODAY() instead of the end date cell reference. TODAY() function would automatically get the current date from your system’s settings.

## Method #2: YEARFRAC and INT Functions to Calculate Months Between Two Dates

Another easy way to calculate the number of months between two dates is by using a combination of YEARFRAC and INT functions

Below I have the same data set where I have the start date and the end date and I want to calculate the total number of months between these two dates in column D:

Here are the steps to do this:

- Select cell
**D2**and type in the formula below:

=INT((YEARFRAC(B2,C2,1)*12))

- Press Enter to get the result. You can then double-click on the fill handle or drag it down to copy the formula down the column.

### Explanation of the formula

=INT((YEARFRAC(B2,C2,1)*12))

The formula uses the **YEARFRAC** and **INT** functions.

The YEARFRAC function takes the start date and the end date as the input arguments and returns a fraction of the year value (based on how many days were there between the start and the end dates)

The syntax of the **YEARFRAC** function:

YEARFRAC(start_date,end_date,[basis])

- The
**start_date**argument is mandatory and represents the starting date of a given period. In our example, it represents the start date of the project. - The
**end_date**is also a mandatory argument and represents the last date of the period. In our example, it represents the last date of the project. - The
**basis**is an optional argument, and it represents the type of day count basis to use. In our example, we used**1**which stands for**actual/actual**basis. If this argument is omitted, the**0**basis that stands for**US (NASD) 30/360**is assumed.

The result of the YEARFRAC function is multiplied by 12 so that we get the total number of completed months.

INT function is then used to extract only the total number of completed months and truncates the decimal portion (which represents the part of the month, but not the complete month)

In case you don’t want only the completed months, and also want the partial month values (such as say 3.5 months), don’t use the INT function. So your formula would be =YEARFRAC(B2,C2,1)*12)

## Method #3: YEAR and MONTH Functions to Get Number Months Between Two Dates

Another easy and straightforward way to get the total number of months between two given dates is by using the YEAR and the MONTH function.

Below I have the dataset and want to calculate the total number of months elapsed between the two dates.

Use the below steps:

- Select cell
**D2**and type in the following formula:

=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2)

- Press Enter to get the result of the formula. Then you can copy the same formula for all the other cells in the column to get the result

### Explanation of the formula

=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2)

This formula uses the **YEAR** and **MONTH** functions.

The YEAR function returns the year of a date in the range 1900-9999.

The MONTH function returns the month of a date, a number from 1 (January) to 12 (December).

This formula subtracts the year of the starting date from the year of the ending date. The result is multiplied by 12 to convert it to months.

The product is then added to the difference between the month of the starting date and the month of the ending date. The result is the total number of months between the two dates.

**Note:** The difference between the month of the starting date and the month of the ending date can be a positive or negative number.

## Method #4: Simple MONTH Formula to Get Number of Months Between Two Dates

Only use this method to calculate the number of months between dates that fall within the same year.

We use the following dataset that has dates that fall within the same year in our illustration.

We use the steps below:

- Select cell D2 and type in the following formula:

=MONTH(C2)-MONTH(B2)

- Press Enter to get the result. You can then double-click on the fill handle or drag it down to copy the formula down the column.

### Explanation of the formula

=MONTH(C2)-MONTH(B2)

The formula uses the MONTH function that returns the month of a given date. The month is returned as an integer ranging from 1 (January) to 12 (December).

**Note**: Excel stores dates as serial numbers.

Syntax of the MONTH function:

MONTH(serial_number)

The **serial_number** argument is a required argument. It represents the date of the month you want to find.

In our example, the MONTH function returns the month of the project’s start date and end date. The month of the start date is then subtracted from the month of the end date. The result is the total number of months between the two dates.

## Method #5: Creating a User Defined Function (UDF) using VBA

If much of your work requires calculating the number of months between dates, creating a dedicated UDF for this task can save you time and effort.

We use the following dataset in our illustration:

We use the following steps:

- In the active worksheet that contains the dataset, press
**Alt + F11**to open the**Visual Basic Editor**. - Click
**Insert >> Module**to insert a module.

- Copy the following function procedure and paste it into the module

'Code by Steve from https://SpreadsheetPlanet.com Function TOTALMONTHS(startDate As Date, endDate As Date) As Integer 'Declaring the variables used in the code Dim startYear As Integer Dim endYear As Integer Dim startDay As Integer Dim endDay As Integer Dim startMonth As Integer Dim endMonth As Integer Dim monthDiff As Integer Dim yearDiff As Integer Dim monthAdjustment As Integer 'Setting values to variables based in the date startYear = Year(startDate) endYear = Year(endDate) startMonth = Month(startDate) endMonth = Month(endDate) startDay = Day(startDate) endDay = Day(endDate) monthDiff = endMonth - startMonth yearDiff = (endYear - startYear) * 12 'IF statement to check and adjust the month value If (monthDiff > 0 Or yearDiff > 0) Then If (startDay <= 15 And endDay >= 15) Then monthAdjustment = 1 ElseIf (startDay >= 15 And endDay <= 15) Then monthAdjustment = -1 End If End If 'Returning the value of the formula TOTALMONTHS = Application.WorksheetFunction.Sum(monthDiff, yearDiff, monthAdjustment) End Function

- Save the procedure and save the workbook as a
**Macro-Enabled Workbook**. - Press
**Alt + F11**to switch back to the active worksheet (or close the VB Editor manually). - Select cell D2 in the dataset and type in the following formula:

=TOTALMONTHS(B2,C2)

**Note:** As you start typing the function, it appears in the list of functions. You can choose it by pressing the tab key as you would the built-in functions.

- Press the Enter key to get the value in cell D2 and then copy the formula for the entire column to get the result for all other cells.

### Explanation of the User-Defined Function

The **TOTALMONTHS** function takes two arguments, the **startDate,** and the **endDate**. The function is of integer data type (which means that it returns an integer as the result).

The function has nine variables of integer data type:

The **Year** function returns the year of the start date and the year of the end date. The year of the start date is assigned to the **startYear** variable, and the year of the end date is assigned to the **endYear** variable.

The **Month** function returns the month of the start date and the month of the end date. The month of the start date is assigned to the **startMonth** variable, and the month of the end date is assigned to the **endMonth** variable.

The **Day** function returns the day of the month of the start date and the day of the month of the end date. The day of the month of the start date is assigned to the **startDay** variable, and the day of the month of the end date is assigned to the **endDay** variable.

The month of the start date in the **startMonth** variable is subtracted from the month of the end date in the **endMonth** variable.

The result is assigned to the **monthDiff** variable.

The difference between the year of the end date in the endYear variable and the year of the start date in the startYear variable is multiplied by 12 to convert to months.

The product is assigned to the **yearDiff** variable.

If the value in the **monthDiff** variable is a positive integer or the value in the **yearDiff** variable is a positive integer and the value in the **startDay** variable is less than or equal to 15 and the value in the **endDay** variable is greater than or equal to 15, an adjustment of 1 is assigned to the **monthAdjustment** variable.

Otherwise, an adjustment of -1 is assigned to the **monthAdjustment** variable.

The sum of **monthDiff**, **yearDiff**, and **monthAdjustment** variables is assigned to the **TOTALMONTHS** function variable.

The function returns this value as the number of months between the two dates passed to it.

In this tutorial, I have covered five different methods you can use to quickly calculate the number of months between two given dates in Excel.

The easiest and fastest way would be to use the DATEDIF function. This function is made to find out the difference between two dates And can give you the results in months, days, or years.

But for some reason, if you want to calculate the number of months without using the DATEDIF function, you can use the combination of YEARFRAC and INT functions or YEAR and MONTH functions.

And if this is something you need to do quite often, then you can also create your own custom formula using VBA.

**Other Excel articles you may also like:**

- Find Last Monday of the Month Date in Excel (Easy Formula)
- How to Convert Month Name to Number in Excel?
- How to Autofill Dates in Excel (Autofill Months/Years)
- How to Change Date and Time to Date in Excel (4 Easy Ways)
- How to Apply Short Date Format in Excel? 3 Easy Ways!
- Using IF Function with Dates in Excel (Easy Examples)
- How to Convert Date to Day of Week in Excel (3 Easy Ways)
- How to Convert Date to Month and Year in Excel (3 Easy Ways)
- Calculate Days Between Two Dates in Excel (Workdays/Weekends)