Quick, clean, and to the point

Calculate years between dates

Excel formula: Calculate years between dates
Generic formula 

To calculate the number of years between two dates, you can use the YEARFRAC function, which will return a decimal number representing the fraction of a year between two dates. In the example shown, the formula in D6 is:


The YEARFRAC function returns a decimal number representing the fractional years between two dates. For example:

=YEARFRAC("1-Jan-2019","1-Jan-2020") // returns 1
=YEARFRAC("1-Jan-2019","1-Jul-2020") // returns 1.5

Here are a few examples of the results that YEARFRAC calculates:

Start date End date YEARFRAC result
1/1/2015 1/1/2016 1
3/15/1970 9/15/1976 6.5
1/1/2000 7/15/2000 .5389
6/1/2000 6/25/1999 .9333

In the example shown, the formula in D6 is:

=YEARFRAC(B6,C6) // returns 1

Rounding results

Once you have the decimal value, you can round the number if you like. For example, you could round to the nearest whole number with the ROUND function:


Whole years only

You might also want to keep only the integer portion of the result with no fractional value, so that you are only counting whole years. In that case, you can just wrap YEARFRAC in the INT function:


If you need to calculate years on an ongoing basis, for example to get age based on a birthday, see the example here.

Note: The YEARFRAC function has an optional 3rd argument that controls how days are counted when computing fractional years. The default behavior is to count days between two dates based on a 360-day year, where all 12 months are considered to have 30 days. The YEARFRAC page provides more information.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.