How do I calculate number of months between two dates in Excel?

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.

Table of Contents

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:

1. Select cell D2 and type in the following formula:

=DATEDIF[B2,C2,”m”]

1. 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:

1. Select cell D2 and type in the formula below:

=INT[[YEARFRAC[B2,C2,1]*12]]

1. 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:

1. Select cell D2 and type in the following formula:

=[YEAR[C2]-YEAR[B2]]*12+MONTH[C2]-MONTH[B2]

1. 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:

1. Select cell D2 and type in the following formula:

=MONTH[C2]-MONTH[B2]

1. 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:

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

1. Copy the following function procedure and paste it into the module

'Code by Steve from //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] Then
        monthAdjustment = 1
    ElseIf [startDay >= 15 And endDay