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

  1. Type the =XDATE(B3,1,4) formula
  2. Press Enter
  3. The formula will add years to a date


=XDATE(date, number, type)


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


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:

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.


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:

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)

Additional resources:

Istvan Vozar

Istvan is the co-founder of Visual Analytics Ltd. He writes blog posts and helps people to reach the top in Excel.