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

This is the sample dataset.

how to subtract dates in excel to get years


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.

Use DATEDIF Function to Subtract Dates in Excel to Get Years

  • 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

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.

Use of Simple Formula with INT

  • 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.

Use of Simple Formula with ROUND

  • 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

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.

3. Insert Excel YEAR Function for Subtracting Dates to Obtain Years

  • 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

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.

Combine INT and YEARFRAC Functions to Deduct Dates

  • 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.

Subtraction of Dates by Joining ROUND and YEARFRAC in Excel

  • 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.

Use LET and YEAR Functions Together to Deduct Dates

  • 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.

Subtract Dates Through Excel VBA to Obtain Years

  • 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 | Calculate Dates | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Lutfor Rahman Shimanto
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo