In Excel, if you want to calculate the count of years that you have between two dates, you can use two different methods. The first method is using the YEARFRAC function and the second is by using the DATEDIF function. Both functions work in the same way, just you need to understand them before using them.
In the following example, you have a start date in cell A1 and an ending date in cell B1.
Using DATEDIF to Get Years Between Two Dates
I have found DATEDIF to be the easiest way to get the count of years between two dates. The only thing that you need to know about DATEDIF: It’s not in Excel’s functions list and you need to know its arguments before you use it.
- First, enter the DATEDIF function in cell C1.
- After that, refer to cell A1, where you have the start date.
- Next, refer to cell B1 where you have the ending date.
- Now, in the third argument, you need to enter the unit “y” to get the count of years.
- In the end, hit enter to get the result.
As you can see in the result we have 7 which is the number of complete years that we have between these two dates. When you use “y” as a unit it only gives you the complete years in the result.
But if you want to get the months and days along with the year, you need to use the formula in the following way.
=DATEDIF(A1,B1,"y")&"y, "&DATEDIF(A1,B1,"ym")&"m, "&DATEDIF(A1,B1,"md")&"d"
Here in the above formula, we have used DATEDIF three times in the same formula but with different units.
Using YEARFRAC to Count Years Between Two Dates
If you don’t want to use the DATEDIF, you can switch to the YEARFRAC. Following, we have the same example that we used earlier.
- First, enter the YEARFRAC in cell C1.
- After that, in the first argument refer to cell A1, where you have the start date.
- Next, in the second argument refer to cell B1, where you have the end date.
- In the end, enter the close parentheses and hit enter to get the result.
Now it has given 7.5 in the result which means we have 7 and a half years in the result. And in the following result, you can see we have used different dates to get a different kind of result.
Here you can see in the second formula that we have used; that we have an exact year between two dates. And in the third formula, we have dates where we have a difference of 6 months, and it has returned .5 in the result.
In the YEARFRAC, you also have a third argument that you can use to change the basics of difference that you want to use to get the years to count between two dates.
From both functions, you can use one which you find the best match in the situation you are in.