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

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.


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])

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:

Calculate Years Between Two Dates in Excel dataset

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.

Calculate Years Between Two Dates in Excel Using the YEARFRAC Function

❹ 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.


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: 

❶ First of all, 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.

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.

Further 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 of all, 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.

Count Years Between Two Date Using the DATEDIF Function in Excel

❹ 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.


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.


Read More

Mrinmoy

Hi! This is Mrinmoy. I write about Excel and VBA stuff for Exceldemy. I have my graduation completed from Khulna University of Engineering & Technology, Bangladesh. I have an immense interest in Datascience and Machine Learning. I like to play guitar and watch Tedtalks in my leisure time. I used to be an indie filmmaker and a wedding photographer. I'm liberal to any kind of constructive criticism and inclined towards expanding my learnings.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo