Calculate Number of Years Between Two Dates in Excel

Let’s say 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 how many years each employee has worked.

In Excel, if you want to calculate the count of years between two dates, you can use different methods. The two main ones are the DATEDIF function and the YEARFRAC function. They don’t work the same way, though: DATEDIF gives you complete years (like 7), while YEARFRAC gives you years with a fraction (like 7.5). Later in this tutorial, I also show the YEAR function, a rounding formula and a custom VBA function.

To understand this, let’s take the example above, where we have two dates: the first is the start date, and the second is the end date. Now, let’s look at the formulas you can use to get the count of years between them.

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 function list, and Excel doesn’t show its arguments as you type, so you need to know them before you use it.

DATEDIF(start_date, end_date, unit)

Here, unit tells DATEDIF what to count: “y” for complete years, “m” for complete months and “d” for days. You’ll also see “ym” and “md” later in this tutorial.

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 end date.
4. Now, in the third argument, you need to enter the unit “y” to get the count of years.
5. In the end, type the closing parenthesis and press Enter to get the result.

=DATEDIF(A1,B1,"y")

As you can see, the result is 7, which is the number of complete years between these two dates. When you use “y” as the unit, it only gives you complete years. But if you want to get the months and days along with the years, you need to use the formula below.

Make sure both cells have valid dates (not dates stored as text) when you calculate the difference between two dates.

=DATEDIF(A1,B1,"y")&"y, "&DATEDIF(A1,B1,"ym")&"m, "&DATEDIF(A1,B1,"md")&"d"

In the above formula, I have used DATEDIF three times with different units: “y” returns the complete years, “ym” returns the months left after those years, and “md” returns the days left after the complete months.

Microsoft warns that the “md” unit can return a negative number, a zero or an inaccurate result, for example, when the start date is near the end of a month. To get the days without “md”, use EDATE instead:

=DATEDIF(A1,B1,"y")&"y, "&DATEDIF(A1,B1,"ym")&"m, "&B1-EDATE(A1,DATEDIF(A1,B1,"m"))&"d"

Here, EDATE moves the start date forward by the number of complete months, and subtracting that from the end date gives you the days left over.

To count the years up to today, for example, to get an employee’s tenure, use TODAY as the end date:

=DATEDIF(A1,TODAY(),"y")

TODAY updates every time the workbook recalculates, so the result always stays current.

One thing to watch: the start date must come first. If the start date is later than the end date, DATEDIF returns the #NUM! error.

YEARFRAC to Calculate Years Between Two Dates

If you don’t want to use DATEDIF, you can switch to YEARFRAC. The following is the same example that we used earlier.

1. First, enter the YEARFRAC function 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, type the closing parenthesis and press Enter to get the result.

Here’s the formula:

=YEARFRAC(A1,B1)

Now, it returns 7.5, which means there are 7 and a half years between the two dates. In the following example, I have used different dates to show different kinds of results.

In the second formula, the two dates are exactly one year apart. And in the third formula, the dates are 6 months apart, and it returns 0.5.

YEARFRAC also has a third argument, basis, that you can use to change the day-count method it uses to count the years between two dates.

If you leave it out, YEARFRAC uses 0 (US 30/360), which treats every month as 30 days. Here are all the options:

• 0 or omitted: US (NASD) 30/360
• 1: Actual/actual
• 2: Actual/360
• 3: Actual/365
• 4: European 30/360

For age or tenure, use 1 (Actual/actual), which counts the real number of days, including leap years.

Of these two functions, use the one that best matches your situation.

If you want YEARFRAC to return whole years only, wrap it in INT, which drops the decimal part, so 7.5 becomes 7:

=INT(YEARFRAC(A1,B1,1))

Use the YEAR Function to Get Years Between Two Dates in Excel

YEAR extracts the year from a date and returns it as a four-digit number. Using it in a formula, you can get the years from two dates and then calculate the difference between them.

=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 date in A1. Then, it subtracts the year in A1 from the year in B1. It’s a quick way to see the year difference without worrying about the specific days and months.

Keep in mind that this formula counts calendar years, not complete years. For example, from 31 December 2023 to 1 January 2024, it returns 1, even though only one day has passed. If you need complete years, use DATEDIF instead.

Rounding Years Between Dates

In this formula, B1 – A1 calculates the difference in days between the two dates, dividing by 365 turns it into years, and ROUND rounds the result to the nearest whole number of years.

=ROUND((B1 - A1) / 365, 0)

The end date is in B1, and the start date is in A1. Once the difference is calculated, it’s divided by 365, and then 0 in num_digits rounds it without any fraction.

Because this formula rounds to the nearest year, it can round up. For example, 6 years and 7 months returns 7. Dividing by 365 also ignores leap days. So use it for a rough count, and use DATEDIF when you need complete years.

Create a Custom Function to Get the 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, open the VBE (Alt + F11), go to Insert > Module, and paste it into the new module. A custom function only works from a standard module like this, not from a sheet’s code window. Then, enter the function in a cell like any other formula (shown below) to get the count of years. This function is flexible, so you don’t need to worry about which date to refer to first.

=GetYearsBetween(A1,B1)

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), or the function is lost when you close the file.

Which Method Should You Use?

• DATEDIF: complete years, like age or years of service. The start date must come first.
• YEARFRAC: years with a fraction, like 7.5. Use basis 1 to count actual days.
• YEAR: the difference between calendar years only. It ignores months and days.
• ROUND: a rough count to the nearest year. It can round up.
• Custom function: complete years with the dates in any order, but it needs a macro-enabled file.

Frequently Asked Questions

=DATEDIF(A1,TODAY(),"y")

=DATEDIF(A1,B1,"y")&" years, "&DATEDIF(A1,B1,"ym")&" months"