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. Show
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 DatesThe 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:
=DATEDIF(B2,C2,”m”)
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)
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 DatesAnother 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:
=INT((YEARFRAC(B2,C2,1)*12))
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 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 DatesAnother 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:
=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2)
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 DatesOnly 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:
=MONTH(C2)-MONTH(B2)
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 VBAIf 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:
'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
=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.
Explanation of the User-Defined FunctionThe 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. How many months between two dates excel?Use the DATEDIF function method if you want to get the total number of completed months in between two dates (it ignores the start date) Use the YEARFRAC method when you want to get the actual value of months elapsed between tow dates.
What is the Excel formula for months?For this, we need to use two functions DATEVALUE & MONTH. The formula to use is =MONTH(DATEVALUE(A2 & “1”)).
|