Mrinmoy Roy
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:
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.
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 within the cell.
=ROUND(YEARFRAC(C5,D5),0) - Then hit the ENTER button.
- Finally, 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) ▶ round up the calculated years calculated by the YEARFRAC
💡 Note:
You can use the formula:
=INT(YEARFRAC(C5,D5))=ROUND(YEARFRAC(C5,D5),0)Read More: Calculate Years and Months between Two Dates in Excel (6 Approaches)
Similar Readings
- How to Apply Excel Formula to Count Days from Date to Today
- Excel Formula for Number of Days Between Two Dates
- How to Find Number of Weeks Between Two Dates in Excel
- Excel Formula to Count Days from Date (5 Easy Methods)
- How to Add Years to a Date in Excel (3 Easy Ways)
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 within the cell.
=DATEDIF(C5,D5,"Y") - Then hit the ENTER button.
- Now drag the 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.
