Add years to date

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

The formula =DATE(YEAR(B3) + D3, MONTH(B3), DAY(B3)) calculates a new date by adding a specified number of years (from D3) to an existing date (from B3), while keeping the original month and day unchanged.

  1. YEAR Function: YEAR(B3) extracts the year from the date in cell B3.
  2. Addition with D3: YEAR(B3) + D3 adds the value in D3 to the extracted year, effectively shifting the date by the specified number of years.
  3. MONTH and DAY Functions: MONTH(B3) and DAY(B3) extract the month and day from B3, preserving these values in the new date.
  4. DATE Function: DATE(…) combines these updated values to form a complete date.

=DATE(2023+2, 5, 13)
=DATE(2025,5,13)
=5/13/2025

Additional resources: