Let’s say you started a year-long project at work on March 01, 2021, and today is May 8, 2024. You want to know how many months have passed since the project started to report on your progress.
By counting the months between the two dates, you can quickly know how long you’ve been working on the project and how many months remain until the target months.
In Excel, to get the number of months between two dates the best way is to use the DATEDIF function. In this function, you need to specify the start date and the end date
After that, you also need to specify the unit in which you want to get the result. Apart from this, we will learn other formulas and methods that you can use to calculate months between two dates.
In our example, we have two dates in cells A2 and B2, you need to write formulas to calculate months between these to dates two dates.
Formula to Number of Months Between Two Sates (DATEDIF)
- First, in cell C2, enter the DATEDIF function.
- After that, in the first argument refer to cell A2 where you have the starting date.
- Next, in the second argument refer to the B2 where you have the ending date.
- Now, in the third argument enter “m”.
- In the end, hit enter to get the result.
Here you can see, that you have got 11 in the result, which is correct as we have 11 months between March 2021 to Feb 2022.
=DATEDIF(A2,B2,"m")
As you have seen when you enter the DATEDIF function Excel doesn’t show the function, and you will not find this function in the list of functions in the list.
You can learn more about DATEDIF from here. You can also use DATEDIF in a way to show months and days separately but in a single cell.
In the above formula, we have used DATEDIF twice to get months and then days and then concatenate both of the formulas to get a complete result.
Months Between Two Dates using YEARFRAC
There might be a situation where we need to count the months between 2 dates, but we also need to consider the days that we have after the completed months. In that case, the best way is to use the YEARFRAC function.
In YEARFRAC, you need to specify the start date in the first argument and then you need to specify the ending date in the second argument. And after that, use the asterisk sign to multiply it by 12.
Now once you hit enter, you will get the total number of months that you have between these two dates as integers and days as decimals.
In the above example, we have the 1st March 2021 as the start date and 23 Feb 2022 as the end date.
Now when I use YEARFRAC, it returns 11.73 where 11 is the number of months and .73 is the decimal value that represents the 23 days that we have in February month.
If you want to round the result, you can wrap this formula with the round function.
Using YEAR + MONTH Function to Get Month Between Two Dates
There’s one more method that you can use to get the count of the month between two dates. By combining YEAR and MONTH functions you can create the following formula that returns months.
=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)
To understand this function, you need to split it into two parts:
In the first part, where we have the year function, it takes the year of the end date and then the year of the start date and then multiplies it with the twelve.
This part returns the number of months that you have between dates.
This part of the formula only makes a difference when you have dates with different years, both dates have the same years then this part will not make any difference to the calculation.
As you can see we have 2021 and 2022 and the result that this part of the formula gives is 12.
In the second part, we are deducting the month of the end date from the month of the start date. And this part also ignores the days we have access.
So, when we deduct March (3) from Feb (2) we get -1 in the result.
After that, in the end, it deducts -1 from the 12 we get 11 which is the total number of months that we have in between both dates. If you ask me which method I like the most, I’d say DATEDIF which is simple to use and easy to understand by the user.
Custom Function for Month Counting
When you count the months between a start and end date, you need to know the days that exceed the completed months; for this, you use a macro to create a custom function.
Function MonthsAndDays(StartDate As Date, EndDate As Date) As String
Dim FullMonths As Long
Dim RemainingDays As Long
Dim AdjustedDate As Date
' Calculate the number of full months
FullMonths = DateDiff("m", StartDate, EndDate)
' Calculate the date after adding full months to the start date
AdjustedDate = DateAdd("m", FullMonths, StartDate)
' Calculate remaining days
RemainingDays = DateDiff("d", AdjustedDate, EndDate)
' Return the result as a string in the format "X months, Y days"
MonthsAndDays = FullMonths & " months, " & RemainingDays & " days"
End Function
To use this function, use Alt + F11 to open VBE and then paste the code into the code window. And then you can use the MonthsAndDays function, in the worksheet.
=MonthsAndDays(A1,A2)
Here, A1 and B1 contain the start and end dates, respectively. It will return a text showing the number of complete months and the days above the months.