Function Problems

Use EDATE to Add Years to a Date in Excel

Maira MehtabMaira Mehtab Nov 12, 2026 869 views

Question details

Add the number of years stored in one cell to an original date in another cell by using the EDATE function.

Use EDATE to Add Years to a Date in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
The original date is in cell AB6, and the number of years to add is in cell P6.
Observed behavior
The formula should return a new date that is the specified number of years after the date in AB6.
Before you start

Confirm that AB6 contains a valid Excel date and P6 contains a numeric number of years. Select the result cell and ensure it is formatted as a Date if the result initially appears as a number.

Solution 1Recommended

Use EDATE to add the years from P6 to the date in AB6

Use this formula when the number of years to add is stored in a worksheet cell and you want the resulting date to update automatically.

EDATE adds a specified number of months to a date. Because one year equals 12 months, multiply the years in P6 by 12.

1
Select the result cell

Click the cell where you want the updated date to appear, such as AC6.

2
Enter the EDATE formula

Type =EDATE(AB6,P6*12) in the selected cell and press Enter.

3
Format the result as a date

If the result displays as a serial number, select the result cell and choose Home > Number Format > Short Date or Long Date.

4
Fill the formula down if needed

Drag the fill handle from the result cell downward to apply =EDATE(AB6,P6*12) to additional rows.

Use EDATE to add the years from P6 to the date in AB6
Formula result: =EDATE(AB6,P6*12) adds the number of years in P6 to the date in AB6 and returns the new date.
Work with dates in WPS Spreadsheets

Add Years to Dates with EDATE in WPS Spreadsheets

WPS Spreadsheets supports the EDATE function, so you can add a variable number of years to dates with the same formula. Enter the formula, apply a date format, and fill it through your worksheet as needed.

  1. 1. Open the worksheet in WPS Spreadsheets: Open your .xlsx or .xls file in WPS Office and go to the worksheet containing the dates.
  2. 2. Choose the output cell: Click the destination cell, such as AC6, where the adjusted date should be displayed.
  3. 3. Enter the formula: Type =EDATE(AB6,P6*12) in the formula bar or directly in the cell, then press Enter.
  4. 4. Set a date format: Select the result cell and use Home > Number Format to choose a Date format.
Use =EDATE(AB6,P6*12) to add years stored in another cell.Apply date number formats from the Home tab.Open and edit Microsoft Excel .xlsx and .xls workbooks with compatible formulas.Use a familiar spreadsheet interface for quick formula migration.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the EDATE result show as a number instead of a date?

Excel stores dates as serial numbers. Select the formula cell, then go to Home > Number Format and select Short Date, Long Date, or another Date format.

Can I add a fixed number of years instead of using a cell reference?

Yes. For example, use =EDATE(AB6,5*12) to add five years to the date in AB6.

How do I subtract years from an Excel date with EDATE?

Use a negative number of months. For example, =EDATE(AB6,-P6*12) subtracts the number of years in P6 from the date in AB6.

Does EDATE work when the date is February 29?

EDATE returns the valid date at the target month position. When the target year does not have February 29, the result is adjusted to the last valid day of February.