30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / How to Add Years to Date in Excel in Excel
By Puneet Gogia (Microsoft MVP)

How to Add Years to Date in Excel in Excel

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

To add years to a date, use EDATE with 12 months per year: =EDATE(A1,12*3) adds three years, so 1-Jan-2021 becomes 1-Jan-2024. Change the multiplier for a different number of years, or point it at a cell — =EDATE(A1,12*B1) — so the result updates when you change B1.

To subtract, use a negative: =EDATE(A1,-12*10) goes back ten years. The alternative is to rebuild the date with DATE: =DATE(YEAR(A1)+3,MONTH(A1),DAY(A1)), which reads more clearly and is easier to adapt. The two differ on one edge case worth knowing: for 29-Feb-2024 plus one year, EDATE returns 28-Feb-2025, while the DATE version rolls over to 1-Mar-2025.

If the result appears as a serial number like 45292, press Ctrl+1 and set the cell format to Date.

Let’s say you work in the IT department of a company, and part of your job is to keep track of when the warranty on various pieces of equipment expires. Ensuring that any necessary repairs or replacements are handled before the warranty runs out is crucial.

Your company has recently purchased several laptops, each with a three-year warranty period. The purchase date for these laptops is Jan 1, 2021. Your task is to calculate the expiration dates of these warranties.

For this, you need to write a formula using the EDATE function. EDATE calculates a date that is a certain number of months (before or after) away from a date. It takes two arguments: the starting date and the number of months you want to add or subtract.

Add a Year in a Date | Excel Formula | Learn Excel

Add 3 Years to a Date in Excel

Here we are using the date 1-Jan-2021 to make it more understandable to you, and you need to add three years to this date, so the date would be 1-Jan-2024.

For this, we will use the EDATE function.

add-date-in-excel
  1. First, you need to enter the EDATE function in cell B1.
  2. After that, in the first argument, refer to cell A1.
  3. Next, enter 12 to specify 12 months.
  4. Now, multiply 12 by 3 to specify 3 years.
  5. In the end, enter the closing parenthesis and press Enter.
edate-function
=EDATE(A1,12*3)

This formula adds 3 years to the date in cell A1 by moving forward by 36 months (12 months for each year, multiplied by 3). If the original date in cell A1 is 01-Jan-2021, then it would return 01-Jan-2024.

If you see a number like 45292 instead of a date, the result cell has the General format. Select it and apply a date format from Home ➜ Number Format ➜ Short Date.

Add 5 Years to a Date

Now, in the same way, if you want to add 5 years to the date, the formula would be something like this.

add-5-years-in-date

If you have the date 01-Jan-2021 in cell A1, it would return 01-Jan-2026 in the result, which is five years ahead of the original date.

=EDATE(A1,12*5)

To keep the number of years flexible, enter it in a cell (like B1) and refer to that cell:

=EDATE(A1,12*B1)

Now, when you change the number in B1, the result updates.

Adding Half a Year

This formula adds six months to the date, which is half a year.

=EDATE(A1, 6)

If you have the date 1-Jan-24 in cell A1, it would return 1-Jul-24 in the result, which is 6 months ahead of the original date.

Adding Fractional Years to a Date

EDATE is a flexible function that allows you to add fractional years to a date. You don’t need to do anything extra but use a fractional value.

=EDATE(A1, 12*2.5)

This formula adds 2.5 years to the date in cell A1 by moving forward by 30 months (12 months for each year, multiplied by 2.5). If the date in cell A1 is 1-Jan-24, then it will return 01-Jul-26 in the result.

Just note that EDATE works with whole months only. If the months aren’t a whole number, it drops the decimal part. For example, 1.3 years is 15.6 months, and EDATE adds 15 months.

Subtract 10 Years from a Date

And if you want to subtract years, let’s say ten years, you can use a formula just like below, with a negative number of months.

=EDATE(A1,-12*10)

For 01-Jan-2021 in cell A1, it returns 01-Jan-2011.

subtract-10-years-from-date

Make sure to have valid dates whenever you are trying to add or subtract a year from a date. If you have a date in the data that is not a valid date as per Excel, all of these formulas will show an error (#VALUE!) in the result.

Other Method to Add and Subtract Years from a Date

You can also use a combination of (Date, Year, Month, and Day) functions to add or subtract years from a date.

=DATE(YEAR(A1),MONTH(A1)+60,DAY(A1))
add-and-subtract-years-from-date

In this formula:

  1. The year function gets the year from the date.
  2. The month function takes a month from the date and then adds five years (60 months) to it.
  3. The day function takes a day from the date.
  4. And the date function, in the end, creates a new date using input from all the functions.

This formula adds 5 years to the date in cell A1 by adding 60 months (12 months for each year). And you can also use the same formula to subtract years from a date.

=DATE(YEAR(A1),MONTH(A1)-60,DAY(A1))
formula-to-add-and-remove-years-from-date

This formula subtracts 5 years from the date in cell A1 by calculating back 60 months (12 months for each year).

EDATE vs DATE on 29 February

The two methods give different results when the date is 29-Feb of a leap year and the new year isn’t a leap year. With 29-Feb-2024 in cell A1:

  • EDATE: adding one year returns 28-Feb-2025, the last day of February.
  • DATE: adding one year to the YEAR part returns 1-Mar-2025, as 29-Feb-2025 doesn’t exist and Excel rolls over to the next day.
=EDATE(A1,12)
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

For anniversaries and expiry dates, EDATE’s result (staying in February) is usually the one you want. The same goes for month ends: EDATE moves 31-Jan to the last day of the target month.

Custom Function to Add Years to a Date

Creating a custom function to add a specified number of years to a date in Excel might sound tricky, but it is simple and easy. Below is the simple VBA code that creates such a function.

Function AddYears(StartDate As Date, NumYears As Integer) As Date
    AddYears = DateAdd("yyyy", NumYears, StartDate)
End Function

In this function, the first argument is StartDate, the date you want to use as the start date. And then, the second argument, NumYears, is the number of years you want to add to the start date (use a negative number to subtract). Like EDATE, DateAdd moves 29-Feb to 28-Feb in a non-leap year.

  1. Press ALT + F11 to open the Visual Basic Editor.
  2. Right-click on any objects in the Project pane.
  3. Go to Insert and then click Module.
  4. Paste the Code in the Code Window of the Module.
  5. Close the editor and use the function in the worksheet (formula below this list). Save the workbook as a macro-enabled file (.xlsm) to keep the function.
=AddYears(A1,3)

Note – Custom functions do not update automatically when you change inputs unless the workbook calculation is automatic. If your function does not update, try recalculating the sheet by pressing F9.

Bonus Code

This is a code which you can use in Excel. With this code, you need to select the cell and run the code, and it will show you an input box. In the input box, you need to enter the number of years you want to add to the date and then click OK.

It will change the date in the selected cells by adding the number of years you have entered. As DateAdd works with whole years, a decimal value like 2.5 is rounded to a whole number. And as the code changes the original dates, you can’t undo it with Ctrl + Z, so keep a copy of your data.

Sub AddYearsToDate()
    Dim rng As Range
    Dim cell As Range
    Dim years As Variant
    Dim inputValid As Boolean

    years = InputBox("Enter the number of years to add:", "Add Years")

    If Not IsNumeric(years) Or years = "" Then
        MsgBox "Please enter a valid numeric value."
        Exit Sub
    End If

    Set rng = Selection

    For Each cell In rng
        If IsDate(cell.Value) Then
            cell.Value = DateAdd("yyyy", Val(years), cell.Value)
        Else
            MsgBox "The cell " & cell.Address & " does not contain a valid date.", vbExclamation
        End If
    Next cell
End Sub

Frequently Asked Questions

How do I add years to today’s date?

Use TODAY as the start date in EDATE. This one returns the date three years from today:

=EDATE(TODAY(),12*3)

Why does EDATE return a #VALUE! error?

The start date is text that Excel can’t read as a date. Convert it into a real date first, and EDATE will work.

Can I add years, months and days in one formula?

Yes, with DATE. This one adds 2 years, 3 months and 10 days to the date in cell A1:

=DATE(YEAR(A1)+2,MONTH(A1)+3,DAY(A1)+10)
Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

Leave a Comment