Calculate Number of Years Between Two Dates in Excel

puneet-gogia-excel-champs

- Written by Puneet

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.

start-and-end-date

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 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.

DATEDIF(start_date, end_date, unit)
datedif-to-get-years
  1. First, enter the DATEDIF function in cell C1.
  2. After that, refer to cell A1, where you have the start date.
  3. Next, refer to cell B1 where you have the ending date.
  4. Now, in the third argument, you need to enter the unit “y” to get the count of years.
  5. 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"
datedif-to-get-years-months-days

Here 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. Following, we have the same example that we used earlier.

yearfrac-to-count-years
  1. First, enter the YEARFRAC in cell C1.
  2. After that, in the first argument refer to cell A1, where you have the start date.
  3. Next, in the second argument refer to cell B1, where you have the end date.
  4. 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.

yearfrac-results

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.

yearfrac-with-third-argument

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.

calculate-years-between-two-dates-excel-with-year-function
=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 from the date 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)
count-years-from-two-dates-withround-function

The end date is in the B1, and the start date is in the A1. Once it calculates the difference, it divides it 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 date without any complexity.

custom-function-to-number-of-years-between-two-dates
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 there in the code window. And the enter the same formula into worksheet to get the count of year. This function is flexible, you don’t need to worry about which date to refer to the first.

Last Updated: May 06, 2024

Leave a Comment