To calculate the number of years between two dates in Microsoft Excel, click on the cell where you want the calculation then enter the formula =YEARFRAC(B1,A1) in the Formula bar and hit Enter to get the result.
Let’s go deeper to examine this procedure in further depth!
Method 1: Using the Formula
Time Estimate: 10 seconds
1. Select the cell where you need to see the result.
2. Go to the Formula bar and type the formula =YEARFRAC(B1,A1), where A1 and B1 are cells with the dates you are comparing.
3. Press the Enter key on your keyboard.
Easy-peasy! Your answer will come back as a decimal number, where the whole number represents the years and the decimal represents the remainder of the months and days.
Method 2: Using the LET Function
Time Estimate: 15 seconds
1. Click on the cell where you want to have the number of years displayed.
2. In the Formula tab, enter the formula =LET(start,B1,end,A1, DATEDIF(start,end,”y”)&” years, “& DATEDIF(start,end,”m”)&” months”). A1 and B1 are the cells with date values.
3. Press Enter.
Here you can see the difference in full years or in the total number of months.
Calculating the Difference in Years in Excel FAQs
If you have any further questions on this topic, have a look at the frequently asked questions and answers below to learn more!
Why is the result of calculating the difference in years shown as a decimal number?
There are a number of reasons why the result of calculating the difference in years between two dates in Excel is often a decimal number.
The first reason is that different months have a different number of days, so when you calculate the difference in days between two dates, that number will include the differing number of days in each month.
Another reason is that leap years have an extra day, so if one of the dates falls on a leap year and the other doesn’t, that will also impact the result.
How to round the decimal value of year in Excel
To round the decimal value of the year, select the cell where you need to calculate the result, go to the Formula bar, type the formula =INT(YEARFRAC(B1,A1)), and press Enter.
If you’re looking for information on how to round numbers, we recommend that you read the following article.
How to autofill dates in Excel
To autofill dates in Excel, select the right corner of the cell with the date and drag it downward to autofill the remaining cells with each successive date.