How to Subtract Dates to Get Years in Excel – 7 Methods

This is the sample dataset.

Method 1 – Using the DATEDIF Function to Subtract Dates to Get Years

The DATEDIF Function calculates the number of days between two dates. If an additional option Y is provided, the DATEDIF returns the number of 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.

Method 2 – Using a Simple Formula to Find Years by Subtracting Dates

Use the INT and the ROUND functions.

2.1 Apply the INT Function

The Excel INT function takes a decimal number and rounds it to the nearest integer.

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

The ROUND function returns a rounded value.

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.

3. Using the YEAR Function to Subtract Dates and Obtain Years

The YEAR Function returns a 4-digit value representing a date.

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.

Method 4 – Combining the INT and the YEARFRAC Functions

The YEARFRAC function estimates the fraction of a year between two dates.

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.

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.

<< Go Back to

Get FREE Advanced Excel Exercises with Solutions!
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF