To add years to date in Excel, you can use the XDATE function instead of built-in functions like YEAR, MONTH, and DAY.
Working with dates in Excel is not rocket science. Despite all that, the built-in functions have limits. Today’s guide will show how to add a formula with a simple and powerful function, XDATE.
How to add years to date
- Type the =XDATE(B3,1,4) formula
- Press Enter
- The formula will add years to a date
Syntax
=XDATE(date, number, type)Arguments
The XDATE function uses three required arguments:
- Date: the date that you want to increase or decrease
- Number: the number of years that you want to add to the date
- Type: This argument can control what you want to add to the given date and uses the following values: “1”: day, “2”: week, “3”: month, “4”: year
Example
In the example, our data set contains different dates in the range B3:B5. The goal is to add various numbers of years to a given date. Configure the function arguments:
- date = B3
- years = 2
- type = 4
Enter the formula in cell D3:
=XDATE(B3,D3,4)
The XDATE function uses the “year” as a third parameter. As usual, the function is a part of our UDF add-in, DatatFX.
Add years using the DATE, YEAR, MONTH, and DAY functions
Here is the generic formula to add a given number of years to an Excel date.
=DATE(YEAR(date)+years,MONTH(date),DAY(date))In the example, use the above-demonstrated data set, and the goal is to solve the problem using regular Excel functions. The formula in cell F3:
=DATE(YEAR(B3)+D3,MONTH(B3),DAY(B3))
Evaluate the formula from the inside out! First, the YEAR, MONTH, and DAY functions extract a date part from the given date.
=YEAR(B3) = 2023
=MONTH(B3) = 5
=DAY(B3) = 13After that, the DATE function creates a valid date using the integer parts.
=DATE(2023+2, 5, 13)
=DATE(2025,5,13)
=5/13/2025
Additional resources:
