This is the sample dataset.
Method 1 – Using the DATEDIF Function to Subtract Dates to Get Years
STEPS:
- Enter the formula in D5:
=DATEDIF(C5,B5,"y")
- Press ENTER to see the result: 6.
- Drag down the Fill Handle to see the result in the rest of the cells.
Read More: How to Calculate Time Difference in Excel Between Two Dates
Method 2 – Using a Simple Formula to Find Years by Subtracting Dates
2.1 Apply the INT Function
STEPS:
- Enter the formula in D5:
=INT((B5-C5)/365)
- Press ENTER to see the result: 6.
- Drag down the Fill Handle to see the result in the rest of the cells.
2.2 Utilize ROUND Function
STEPS:
- Enter the formula in D5:
=ROUND((B5-C5)/365,0)
- Press ENTER to see the result: 6.
- Drag down the Fill Handle to see the result in the rest of the cells.
Read More: How to Calculate Time Difference Between Two Dates in Minutes in Excel
3. Using the YEAR Function to Subtract Dates and Obtain Years
STEPS:
- Enter the formula in D5:
=YEAR(B5)-YEAR(C5)
- Press ENTER to see the result: 6.
- Drag down the Fill Handle to see the result in the rest of the cells.
Read More: Difference Between Two Dates in Months in Excel
Method 4 – Combining the INT and the YEARFRAC Functions
STEPS:
- Enter the formula in D5:
=INT(YEARFRAC(C5,B5))
- Press ENTER to see the result: 6.
- Drag down the Fill Handle to see the result in the rest of the cells.
Read More: How to Minus Number of Days or a Date from Today in Excel
Method 5 – Subtracting Dates by combining the ROUND and YEARFRAC in Excel
STEPS:
- Enter the formula in D5:
=ROUND(YEARFRAC(C5,B5),0)
- Press ENTER to see the result: 6.
- Drag down the Fill Handle to see the result in the rest of the cells.
Method 6 – Using the LET and the YEAR Functions
STEPS:
- Enter the formula in D5:
=LET(sDate,YEAR(C5),eDate,YEAR(B5),eDate-sDate)
- Press ENTER to see the result: 6.
- Drag down the Fill Handle to see the result in the rest of the cells.
Method 7 – Subtracting Dates to Obtain Years with Excel VBA
STEPS:
- Select the active sheet.
- In Developer, choose Visual Basic.
- Click Insert and select Module.
- Enter 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
- Press F5 or click Run.
This is the output.
Download Practice Workbook
Download the workbook here.
<< Go Back to Subtract Dates | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
doesn’t work
Hello AD,
Sorry to hear the issue. We have shown 7 different methods to subtract dates to get years in Excel. Would you please specify which one is not working and what types of error you are getting?
Please explain your issue in detail to help you out.
Regards
ExcelDemy