How to Calculate Years Between Two Dates in Excel (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

It is quite a common incident to work with dates while working in Excel. Very often we may need to calculate dates or years between two given dates. Fortunately, Excel has built-in functions that can be used to calculate years in between dates. Keeping that in mind, in this article, we will be discussing how to use those functions to calculate years between two dates in Excel with ease.

overview of calculating year difference between two dates


Download the Practice Workbook

You are recommended to download the Excel file and practice along with it.


The YEARFRAC Function: an Overview

The YEARFRAC function calculates the years in between two given dates.

Syntax

=YEARFRAC(start_date, end_date, [basis])

YEARFRAC Function Syntax

Arguments

  • start_date ▶ This field is required. This one refers to the start date of an event.
  • end_date ▶ This field is required. This one refers to the end date of an event.
  • basis ▶ This field is optional. The type of day count basis to use.
Basis Day count basis
0 or omitted US (NASD) 30/360
1  

Actual/actual

2  

Actual/360

3  

Actual/365

4  

European 30/360


The DATEDIF Function: an Overview

This function calculates the number of days, months, or years in between two dates.

Syntax

=DATEDIF(start_date,end_date,unit)

Arguments

  • start_date ▶ This field is required. This one refers to the start date of an event.
  • end_date ▶ This field is required. This one refers to the end date of an event.
  • unit ▶ This field is required. It refers to the format of dates that you want to be returned, where:
Unit  Returns
“Y” The number of complete years.
“M” The number of complete months.
“D” The number of days.
“MD” The difference between the days in start_date and end_date.
“YM” The difference between the months in start_date and end_date.
“YD” The difference between the days of start_date and end_date.

2 Methods to Calculate Years Between Two Dates in Excel

In this article, we will be using a sample project time-tracker as a dataset to demonstrate all the methods. So, let’s have a sneak peek of the dataset:

dataset containing different project names with starting and ending date that we will as examples to Calculate Years Between Two Dates in Excel

So, without having any further discussion let’s dive straight into all the methods one by one.


1. Calculate Years Between Two Dates in Excel Using the YEARFRAC Function

Functions Used: YEARFRAC

In this section, we will calculate the years of duration between each of the projects included within the data table based on their starting date and ending date. Now follow the steps below to see how it works:

🔗 Steps: 

  • First of all, select cell E5 ▶ to store the formula result.
  • After that type the formula
    =YEARFRAC(C5,D5)
    within the cell.
  • Then hit the ENTER button.

applying the YEARFRAC Function for calculating years Between Two Dates in Excel

  • Now drag the Fill Handle icon to the end of the Duration column.

Fill Handle icon to the end for the rest of the Duration column

That’s it.


1.1 Round Up the Calculated Years

Functions Used: YEARFRAC ROUND

As you have seen, the years that have been calculated are all infractions. Now in this section, we will round up those fraction values to their nearest integer values. To do so,

🔗 Steps: 

  • At first, select cell F5 ▶ to store the formula result.
  • After that type the formula
    =ROUND(YEARFRAC(C5,D5),0)
    within the cell.
  • Then hit the ENTER button.
  • Finally, drag the Fill Handle icon to the end of the Rounded Year column.

applying Round function to round Up the Calculated Years

␥  Formula Breakdown

  • YEARFRAC(C5,D5) calculates years between two dates.
  • =ROUND(YEARFRAC(C5,D5),0) round up the calculated years calculated by the YEARFRAC

💡 Note:

You can use the formula:

=INT(YEARFRAC(C5,D5))
instead of the previous formula,
=ROUND(YEARFRAC(C5,D5),0)
to round up years to the nearest integer values.

Read More: Calculate Years and Months between Two Dates in Excel (6 Approaches)


Similar Readings


2. Count Years Between Two Dates Using the DATEDIF Function in Excel

Functions Used: DATEDIF

Now we will use the DATEDIF function two calculate years in between two dates. Now follow the steps below:

🔗 Steps: 

  • First, select cell E5 ▶ to store the formula result.
  • After that type the formula
    =DATEDIF(C5,D5,"Y")
    within the cell.
  • Then hit the ENTER button.

applying the DATEDIF Function for finding duration in years

  • Now drag the Fill Handle icon to the end of the Duration column.

Fill Handle icon to the end of the Duration column.

That’s it.

Read More: How to Calculate Years in Excel from Today (4 Ways)


Things to Remember

  • Be aware of the syntax of the functions.
  • Carefully insert the range of calculation inside the formulas.

Conclusion

To sum up, we have discussed 2 easy techniques to calculate years between two dates in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website ExcelDemy to explore more.


Further Readings

Mrinmoy Roy
Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo