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.
Also read: How to Get Total Days in Month in Excel?
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)
Also read: Calculate Days Left in the Year (Formula)
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.
Also read: Count Days from a Date to Today in Excel (Formula)
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.
Also read: Convert Days to Months in Excel
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)
I have something a little more complicated that I have not had any luck with. Would you be able to help?