How to count number of months between two dates in Excel
The best way to understand how DATEDIF works is to first understand the structure of the function and the parameters it requires.
Structure of DATEDIF
The standard syntax of countif function in excel is shown below;
=DATEDIF(start_date, end_date, unit)
- start_date
Could be any date earlier than the second parameter end date. Otherwise errors will occur.
- end_date
Could be any date earlier than the second parameter end date. Otherwise errors will occur.
- unit
Could be any one from “d” for days, “m” for months and “y” for years.
Implementing the Formula on an Actual Data Set
Let’s take an example data set and find out the number of days between some actual dates. So let’s consider the data set shown below;
We can see in the example data set that there are some start dates that are earlier than the end dates.
Let’s see how we can use Excel’s built-in formula to find a solution to our problem by following these steps;
Sometimes we are required to handle the data that involves recursive calculations involving dates. Counting the number of months between two dates while working in Excel is quite common. Excel provides a formula for calculating the number of months between dates.
Excel has the built-in function to calculate the date difference through the “DATEDIF” function. However, this formula is not included in the standard formula list by Excel. Which means that when you type this formula Excel will not show its help just like it does while typing in other standard formulas. So it’s kind of a hidden (secret) formula.
Step 1 – Create the formula to calculate Months Difference:
– Choose a suitable cell where you wish to implement the formula. In our example it is C2.
– Use the following formula in that cell and press enter key.
= DATEDIF(A2,B2,”m”)
Step 2 – Implement the formula to calculate Months Difference:
– Now we will implement the formula to calculate the difference of months between two dates. After writing the formula just press the enter key. The result will be displayed in the cell. To extend the formula drag down the formula as shown in the figure. So by using Excel’s DATEDIF function we can easily find the difference of months between two dates.