Let’s you’re an HR manager and you need to figure out how long each employee has been with your company. You have their start date and today’s date. In Excel, you can easily calculate the number of years between these two dates to know the years each employee has worked.
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.
To understand this let’s take an example we have above where we have two dates, one if the start date and the second is the end date. Now let’s understand the formula which we can use to get count of years between.
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. It calculates the difference between two dates in days, months, or years, based on the unit you specify. It’s quite useful for determining exact age, tenure, or intervals in years.
The only thing you need to know about DATEDIF is that it’s not in Excel’s functions list, and you need to know its arguments before you use it.
DATEDIF(start_date, end_date, unit)
- 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.
=DATEDIF(A1,B1,"y")
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.
Make sure to have valid dates, when you are subtracting two dates to get the years difference.
=DATEDIF(A1,B1,"y")&"y, "&DATEDIF(A1,B1,"ym")&"m, "&DATEDIF(A1,B1,"md")&"d"
In the above formula, we have used DATEDIF three times in the same formula but with different units.
YEARFRAC to Calculate Years Between Two Dates
If you don’t want to use the DATEDIF, you can switch to the YEARFRAC. The following is 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. 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.
Use YEAR Function to Get Years Between Two Dates in Excel
YEAR extracts the year from a date, returning it as a four-digit number. Now using it in a formula you can get years from two dates and then check for the difference between two.
=YEAR(B1) - YEAR(A1)
Here, you have a start date in cell A1 and an end date in cell B1. It extracts the year from the date in B1 and then from the year in A1. Then, it subtracts the year from A1 from the year in B1. It’s a quick way to see the year difference without worrying about the specific days and months.
Rounding Years Between Dates
In this formula, ROUND calculates the difference in days between the two dates, divides by 365, and rounds the result to get the nearest whole number of years.
=ROUND((B1 - A1) / 365, 0)
The end date is in the B1, and the start date is in the A1. Once the difference is calculated, it is divided by 365, and then we have 0 as the num_digit to round it without any fraction.
Create a Custom Function to Get Years Count
You can create a custom function with this code, which allows you to get the number of years between two dates without any complexity.
Function GetYearsBetween(Date1 As Date, Date2 As Date) As Integer
' Check which date is earlier to handle the calculation correctly
Dim StartDate As Date, EndDate As Date
If Date1 < Date2 Then
StartDate = Date1
EndDate = Date2
Else
StartDate = Date2
EndDate = Date1
End If
' Calculate the difference in years
Dim Years As Integer
Years = Year(EndDate) - Year(StartDate)
' Adjust if end date's month and day are before start date's month and day
If Month(EndDate) < Month(StartDate) Or (Month(EndDate) = Month(StartDate) And Day(EndDate) < Day(StartDate)) Then
Years = Years - 1
End If
' Return the result
GetYearsBetween = Years
End Function
To add this code, go to the VBE (Alt + F11) and paste it into the code window. Then, enter the same formula into the worksheet to get the count of the year. This function is flexible, so you don’t need to worry about which date to refer to first.