Microsoft Excel is undoubtedly a highly efficient program. Excel’s tools and functions allow us to execute endless actions on a dataset. This article discusses 7 straightforward techniques for subtracting dates to obtain years. Therefore, you should use these 7 simple methods to Subtract Dates in Excel to Get Years.
Download Practice Workbook
Please obtain a free copy of the sample workbook used during the session.
7 Simple Methods to Subtract Dates in Excel to Get Years
For illustration, let’s investigate a sample dataset. For instance, the following dataset has a First date, a Second date, and a Year difference column. We will use each of the 7 methods to determine the year by subtracting these dates in Excel. In addition, I have yet to say that I have been working with the Microsoft Excel 365 version for this post. Nevertheless, you are free to use any other edition that will provide you with the most significant amount of convenience.
1. Use DATEDIF Function to Subtract Dates in Excel to Get Years
The DATEDIF Function typically calculates the number of days between two dates. However, if an additional option Y is provided, like in this example, DATEDIF returns the number of years. DATEDIF is the easiest way to find out how many years are between two dates. Consequently, follow the procedures below to complete the task.
STEPS:
- First, I input the formula in cell D5:
=DATEDIF(C5,B5,"y")
- Second, press the ENTER key to view the answer, which is 6.
- Now, Apply the same procedure in cell D5 in other cells to calculate the years between two dates.
- To get this, click on the Fill Handle icon.
- Importantly, hold it down as well as drag it to cell D10.
- Consequently, it will return the desired output, as demonstrated below.
Read More: How to Use DateDiff Function in Excel VBA (5 Examples)
2. Employ Simple Formula to Find Years by Subtracting Dates
Alternatively, we can calculate the days between two dates and divide the result by 365 to get the year. In this way, the fraction value comes out, so we will take the help of the INT Function and ROUND Functions.
2.1 Apply INT Function
The Excel INT function takes a decimal number and rounds it down to the nearest integer. Follow the below instructions to complete the task.
STEPS:
- First, I began by entering the formula in cell D5:
=INT((B5-C5)/365)
- Second, press the Tab key to see the solution, which is 6.
- Now, apply the same calculation in cell D5 to different cells to get the years between two dates.
- To get this, select the Fill Handle icon.
- Essential, press and drag to cell D10.
- Finally, the desired output will be returned, as seen below.
2.2 Utilize ROUND Function
Excel’s ROUND function returns a rounded value. To do the task successfully, follow the processes that are outlined below.
STEPS:
- First of all, I entered the following formula in cell D5:
=ROUND((B5-C5)/365,0)
- Second, press the Enter key to view the answer in cell D5.
- Now, apply the procedure in cell D5 to several cells to determine the number of years between two dates.
- To get this, tap the Fill Handle icon.
- Essentially, hold it as well as drag it to cell D10.
- As a result, It will return the desired output.
Read More: Excel Formula to Find Date or Days for Next Month (6 Quick Ways)
3. Insert Excel YEAR Function for Subtracting Dates to Obtain Years
The YEAR Function provides a 4-digit value representing a date’s year portion. Using the YEAR function is another way to calculate the year based on two dates. To do the task properly, please follow the directions that are listed below.
STEPS:
- First, continue by entering the formula shown below in cell D5:
=YEAR(B5)-YEAR(C5)
- Second, press the Tab key to see the response in cell D5.
- To calculate the amount of time between two dates, use the formula in cell D5 in multiple cells.
- Next, tap the Fill Handle icon.
- After that, keep holding and drag to cell D10.
- Finally, It will yield the desired result, as seen below.
Read More: How to Calculate Years in Excel from Today (4 Ways)
Similar Readings
- How to Calculate 90 Days from a Specific Date in Excel
- How to Add Dates in Excel Automatically (2 Simple Steps)
- Add 30 Days to a Date in Excel (7 Quick Methods)
- How to Use COUNTIFS with Date Range in Excel (6 Easy Ways)
- Excel Formula to Calculate Number of Days Between Today and Another Date
4. Combine INT and YEARFRAC Functions to Deduct Dates
Here, the YEARFRAC function estimates the fraction of a year between two dates. Combining the INT and YEARFRAC functions is an alternative method for calculating the year given two dates. To accomplish the task effectively, please follow the instructions stated below.
STEPS:
- First, enter the formula in cell D5:
=INT(YEARFRAC(C5,B5))
- Secondly, press Tab to view cell D5‘s answer.
- Subsequently, use cell D5‘s formula to compute the interval between two dates.
- Then, tap Fill Handle.
- Later, drag to D10.
- Hence, the intended outcome is below.
Read More: How to Calculate Years Between Two Dates in Excel (2 Methods)
5. Subtraction of Dates by Joining ROUND and YEARFRAC in Excel
Combining ROUND and YEARFRAC can also calculate the year given two dates. Follow these instructions to complete the assignment.
STEPS:
- First, select cell D5.
- Second, input the formula below.
=ROUND(YEARFRAC(C5,B5),0)
- Then, hit Enter to see cell D5‘s result.
- Next, use cell D5‘s formula to calculate the time between two dates.
- Now, tap the Fill Handle icon.
- At this time, hold it as well as drag it to D10.
- As a result, below is the desired output.
Read More: How to Minus Number of Days or a Date from Today in Excel
6. Use LET and YEAR Functions Together to Deduct Dates
The LET function in Excel permits assigning names to computation results and defining variables inside a formula. Another option is using LET and YEAR to get the year from two dates. Follow these steps to complete the task.
STEPS:
- First, I inserted the following formula in cell D5:
=LET(sDate,YEAR(C5),eDate,YEAR(B5),eDate-sDate)
- After that, press Enter to display the answer in cell D5.
- Now, use the method used in cell D5 to numerous cells to calculate the years between two dates.
- To acquire this, click the Fill Handle icon.
- Essentially, hold, and drag to cell D10.
- Finally, as seen below, it will return the desired result.
Read More: Excel Formula to Count Days from Date (5 Easy Methods)
7. Subtract Dates Through Excel VBA to Obtain Years
In Excel, we may subtract two dates using VBA to obtain the years. Please follow the below guidelines to finish the task efficiently.
STEPS:
- To begin, choose the active sheet of the worksheet.
- Secondly, navigate to Developer.
- Next, choose Visual Basic.
- Afterward, click Insert, followed by Module.
- Now, paste the code below into the Module Box.
Sub YearsBetweenTwoDatesSofteko()
Dim StartDate As Date
Dim EndDate As Date
Dim yearDif As Integer
For i = 5 To 10
StartDate = Cells(i, 3).Value
EndDate = Cells(i, 2).Value
yearDif = DateDiff("yyyy", StartDate, EndDate)
Cells(i, 4).Value = yearDif
Next
End Sub
- After that, press F5 or select the Run button to conclude.
- Thus, Here will be the output.
Read More: Calculate Number of Days between Two Dates with VBA in Excel
Conclusion
In summary, you can Subtract Dates in Excel to Get Years using the steps we just discussed from now on. On the ExcelDemy website, you can find many articles like this one. Keep using those, and let us know if you have any other ways to get the work done or if you have any new ideas. Last but not least, remember to post questions, comments, or suggestions in the section below.
Related Articles
- How to Calculate Remaining Days in Excel (5 Methods)
- How to Calculate Working Days between Two Dates in Excel (4 Methods)
- [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
- How to Count Days from Date to Today Automatically Using Excel Formula
- Calculate Expiration Date with Excel Formula
- How to Calculate Days Outstanding in Excel (With Easy Steps)