How to Add Years to a Date in Excel

The simplest way to add years to a date

To add years to a date in Excel, use the DATE function combined with the YEAR function. This method works in all versions of Excel and handles leap years automatically.

The formula is: =DATE(YEAR(A1)+5, MONTH(A1), DAY(A1))

Replace A1 with the cell containing your date, and replace 5 with the number of years you want to add. This formula extracts the year from your original date, adds your number to it, then rebuilds the date with the same month and day. If your original date is February 29 and you add years that land on a non-leap year, Excel automatically adjusts to February 28.

Key Takeaways

The DATE and YEAR formula works in every version of Excel and handles all edge cases, including leap years.
You can subtract years by using a negative number in the formula, such as =DATE(YEAR(A1)-3, MONTH(A1), DAY(A1)).
If you need to add years to many dates at once, enter the formula once and copy it down the column.
The EDATE function can also add years, but it requires you to multiply your years by 12 since it works in months: =EDATE(A1, 5*12).

Setting up your spreadsheet

Start by putting your original dates in one column. For this example, use column A. Your dates should be formatted as actual dates, not text — if Excel doesn't recognize them as dates, the formula won't work.

Click on the cell next to your first date (cell B1) and type the formula. Make sure you start with an equals sign so Excel knows you're entering a formula, not text. After you type the formula and press Enter, Excel calculates the new date when ready.

If you see a number like 45000 instead of a date, the cell is formatted as a number. Right-click the cell, select Format Cells, choose Date from the Category list, and click OK. The number will convert to a readable date.

Copying the formula to multiple rows

If you have 50 dates and need to add years to all of them, you don't have to type the formula 50 times. Enter the formula in cell B1, then click on that cell to select it. Look for the small square in the bottom-right corner of the cell — this is the fill handle.

Click and drag the fill handle down to the last row with data. Excel copies the formula down and automatically adjusts the cell reference for each row. Row 2 will reference A2, row 3 will reference A3, and so on. This takes seconds and eliminates typing errors.

If dragging feels awkward, select cell B1, then hold Shift and click on the last cell where you want the formula (for example, B50). Press Ctrl+D on Windows or Cmd+D on Mac, and Excel fills down the formula to all selected cells.

Using EDATE if you prefer months

The EDATE function adds or subtracts months from a date. To use it for years, multiply your years by 12. The formula is: =EDATE(A1, 5*12) to add 5 years.

EDATE and DATE produce the same result in most cases, but EDATE handles certain edge cases differently. If your original date is January 31 and you add one month, EDATE returns February 28 (or 29 in a leap year), while DATE would also return February 28. For adding years, both functions are equally reliable.

Use whichever formula feels clearer to you. The DATE formula is more explicit about what you're doing — adding to the year component — while EDATE is shorter to type if you're comfortable thinking in months.

Subtracting years instead of adding them

To subtract years, use a negative number in the same formula. For example, =DATE(YEAR(A1)-3, MONTH(A1), DAY(A1)) subtracts 3 years from the date in A1.

This is useful when you need to find a date that was a certain number of years in the past — for instance, calculating a birth date from an age, or finding when a contract began based on its anniversary date. The formula works exactly the same way as adding, just with a minus sign instead of a plus sign.

Checking your results

After you enter the formula, spot-check a few results by hand. If your original date is March 15, 2020, and you add 5 years, the result should be March 15, 2025. If the month or day changed unexpectedly, double-check that your original dates are formatted as dates and not text.

If a date appears to be wrong, click on the cell with the formula and look at the formula bar at the top of the screen. You'll see the actual formula Excel is using. Verify that the cell reference (like A1) points to the correct date, and that your year adjustment is the number you intended.

Common mistakes and how to avoid them

The most common error is forgetting the equals sign at the start of the formula. Without it, Excel treats your entry as text and displays the formula itself rather than calculating a result. Always start with =.

Another mistake is using text dates instead of real dates. If your dates are stored as text (often because they were imported from another program), the YEAR function won't recognize them. You can test this by clicking on a date cell — if the formula bar shows the date in quotes like "3/15/2020", it's text. Converting text dates to real dates requires a separate step, so it's easier to avoid this by ensuring dates are formatted correctly when you first enter them.

A third mistake is adjusting the formula when you copy it down. Don't change the cell references manually — let Excel do it for you. If you copy the formula and the results look wrong, you may have accidentally edited the formula in one of the copied cells.

Frequently Asked Questions

What if my date is in a different column or has a different format?

The formula works the same way regardless of which column your date is in. Just replace A1 with the correct cell reference. If your dates are formatted differently (like "March 15, 2020" instead of "3/15/2020"), as long as Excel recognizes them as dates, the formula will work. The display format doesn't affect the calculation.

Can I add a decimal number of years, like 2.5 years?

The DATE formula works with whole numbers only. If you need to add 2.5 years (2 years and 6 months), use EDATE instead: =EDATE(A1, 2.5*12). This multiplies 2.5 by 12 to get 30 months, which Excel then adds to your date.

What happens if I add years to February 29?

Excel automatically adjusts to February 28 if the resulting year is not a leap year. For example, if your original date is February 29, 2020, and you add 1 year, the result is February 28, 2021. This is the standard way spreadsheets handle this edge case, and it's usually the behavior you want.

Can I add years and months at the same time?

Yes, using EDATE. The formula =EDATE(A1, 5*12+3) adds 5 years and 3 months. Multiply your years by 12, then add the number of additional months you want. With the DATE function, you would need a more complex formula, so EDATE is simpler for this task.

Why does my formula show an error like #VALUE!?

This error usually means Excel doesn't recognize the cell you referenced as a date. Check that the cell contains an actual date, not text that looks like a date. Also verify that you spelled the function names correctly — it's YEAR and DATE, not YEARS or DATES. If you copied the formula from another source, make sure there are no extra spaces or special characters.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.