Add years to date

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 in Excel?

  1. Type the =XDATE(B3,1,4) formula
  2. Press Enter
  3. 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)

add-years-to-date-excel-xdate

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))

how-to-use-the-DATE-YEAR-MONTH-DAYS-functions

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) = 13

After 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: