How to Calculate Years Between Two Dates in Excel

Welcome to another Excel shorty. We greet you and some calculations for today’s lesson on calculating years between two dates in Excel. Time is money and therefore in your work life especially, you will often find time at the center stage.

With time in the limelight, we will explore different formulas today to calculate the years between two dates.

Our formulas will include the YEARFRAC, DATEDIF, and the INT and ABS functions. Now let’s have a look at where we will apply these formulas.

Example

As our example for this guide, we will be calculating establishment years of a list of famous brands. To calculate the years, we need to find the difference between the founding date and the current date. Now a simple subtraction of the two values won’t give us that so we have to apply a different technique.

Note: For the purpose of this tutorial, we have entered a dummy date as the current date. In all the formulas below, the current date can be swapped with the TODAY function to create a dynamic result that will recalculate every day. Use the following argument with the TODAY function in place of the end date:

Now that we know where we will be applying our formulas, we can get down to how we will apply them.

Let’s get calculating!

Method #1 – Using YEARFRAC Function

The first formula to calculate years between two dates involves the YEARFRAC function which brings us to what the function does. The YEARFRAC function returns the years between the start and end dates as a fraction. So let’s give the YEARFRAC function a start date and an end date and see what happens:

Using our case example, we have supplied the YEARFRAC function very simply with the founding date in cell C3 as the start date and the current date in D3 as the end date. YEARFRAC calculates the years between the two dates and returns 48.8 as the outcome.

The difference of years between the rest of the dates has been computed with the same formula:

Use the Increase and Decrease Decimal buttons in the Home tab’s Number group to adjust the decimals:

Notes:

To round the number off, you can use the Decrease Decimal button until the results are in integers or you can wrap the formula in the ROUND function like:

By adding the TODAY function in the formula in place of the end date, the formula will be:

Method #2 – Using DATEDIF Function

The DATEDIF function returns the years, months, or days between two dates.

You’ve started typing the formula to apply DATEDIF by it doesn’t show up in the Formula AutoComplete, right? The problem is not with your version of Excel; the DATEDIF function is an undocumented function. You can find its syntax and usage here.

Calculating Number of Complete Years

Below is a simple application of the DATEDIF function to return the number of complete years:

With the start date and end date, the DATEDIF function has been supplied with the “y” unit. This unit stands for “years” and will calculate the number of complete years between the two dates.

The following results of the DATEDIF function are the complete number of years between the founding date and current date:

Calculating Nearest Whole Years

From the YEARFRAC function, we know that the establishment duration in the first instance is 48.8 years. With the DATEDIF function, we had arrived at the complete number of years i.e. 48. For calculating the nearest whole years (which we can deduce as 49 years from the fraction), we need to make a small adjustment in the formula:

Notice the adjustment? We have added 150 to D3. This adds 150 days to the end date to adjust the function to return the nearest whole years. Evident in the results, the number of years has been rounded off to the nearest whole number:

Calculating Years, Months, Days

If you’re not happy with a single number and would prefer a very specific result, you can use the DATEDIF function repeatedly to return the exact number of years, months, and days between two dates. For extra flair, we will use the ampersand (&) operator to add text to the results. Let’s show you what we mean following the formula below:

For now, let’s ignore the & interjections. The first DATEDIF in the formula returns the complete number of years between C3 and D3 as mentioned in the section above. The second DATEDIF returns the number of months between C3 and D3. The unit used for the months is “ym” instead of “m” to return the months independent of the complete years. The third DATEDIF returns the days independent of the full months between C3 and D3, using the unit “md”.

Now for the & operator. The ampersand pieces the individual DATEDIFs into one formula. We have also added the text “Y” for year(s), “M” for month(s), and “D” for day(s). Here is what the collated formula and its results look like:

You can also break this formula down and have the years, months, and days in separate columns. You can remove the added text. You can also customize the added text to “years”, “months”, and “days”.

Method #3 – Using Custom Formula (Calculating Complete Years)

Our final technique hosts a custom formula with the INT and ABS functions working together to calculate years between two dates. The INT function rounds a number down to the nearest integer. The ABS function returns the absolute value of a number without its sign. Now let’s take a look at how they work for calculating years between two dates:

Firstly the ABS function processes C3-D3 and returns the number of days between the two dates as 17807. Dividing 17807 with 365 results in the number of years, 48.78 in this case. The INT function then rounds the number down to 48 which is the final value of the formula.

The formula’s results for our list are:

Method #4 – Using Custom Formula (Calculating Nearest Whole Years)

And now instead of the adjustment of 150 days, you could also remove the INT function that is rounding the numbers down to at the nearest number of whole years. With the INT function removed, the formula becomes:

The ABS function computes the difference in days between the two dates, returning the absolute value which is divided by 365 and we have our result:

Between you and us, we’re done! That was our tutorial on how to calculate years between two dates. With our many variations, we hope you find what you’re looking for; be it computing full years or the nearest number of whole years.

And only for the while, that’s enough calculating because we’ll be back with more calculations that will be mind joggers and finger runners for you to keep up! See you at the other end of another Excel exercise!