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.
The YEARFRAC Function: An Overview
The YEARFRAC function calculates the years 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 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. |
Calculate Years Between Two Dates in Excel: 2 Methods
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.
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 within the cell.
=YEARFRAC(C5,D5)- Then hit the ENTER button.
- Now drag the Fill Handle icon to the end of the Duration column.
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.
🔗 Steps:
- Select cell F5 to store the formula result.
- After that type the formula.
=ROUND(YEARFRAC(C5,D5),0)- Then hit the ENTER button.
- Drag the Fill Handle icon to the end of the Rounded Year column.
␥ Formula Breakdown
- YEARFRAC(C5,D5) ▶ Calculates years between two dates.
- ROUND(YEARFRAC(C5,D5),0) ▶ The ROUND function rounds up the calculated years.
💡 Note:
You can use the formula:
=INT(YEARFRAC(C5,D5))=ROUND(YEARFRAC(C5,D5),0)Read More: How to Calculate Years from Today in Excel
2. Count Years Between Two Dates Using the DATEDIF Function in Excel
Functions Used: DATEDIF
We will use the DATEDIF function to calculate the years between two dates.
🔗 Steps:
- Select cell E5 and type the formula below.
=DATEDIF(C5,D5,"Y")- Then hit the ENTER button.
- Now drag the Fill Handle icon to the end of the Duration column.
Read More: Calculate Years and Months Between Two Dates in Excel
Things to Remember
- Be aware of the syntax of the functions.
- Carefully insert the range of calculation inside the formulas.
Download the Practice Workbook
You are recommended to download the Excel file and practice along with it.
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. Please visit our website to explore more.
Further Readings
- Excel Calculates Difference Between Two Dates in Days
- Excel Formula to Calculate Number of Days Between Today and Another Date
- Excel Formula to Count Days from Date
- How to Calculate Expiration Date with Excel Formula
- How to Calculate Number of Months Between Two Dates in Excel
- How to Find Number of Weeks Between Two Dates in Excel
- How to Count Months from Date to Today by Using Excel Formula
- Calculate Number of Days between Two Dates with VBA in Excel
<< Go Back to Days Between Dates | Calculate Dates | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
