To add years to a date in Excel, use =EDATE(A2,12*B2), where A2 contains the starting date and B2 contains the number of whole years to add. For example, adding 5 years to a date in A2 returns the date 60 months later; EDATE also handles short months and leap-day dates more predictably than simply changing the year number.
The quick formula for adding years
Excel stores dates as serial numbers, so adding a number directly to a date adds days, not years. If A2 contains 15-Jun-2024 and B2 contains 3, the formula =EDATE(A2,12*B2) returns 15-Jun-2027. The multiplication converts years into months because EDATE expects a number of months.
For a fixed number of years, you can put that number directly in the formula. With the starting date in A2, =EDATE(A2,60) adds five years. A cell reference is usually more convenient if you may change the period, fill the formula down a list, or calculate different terms for different rows.
Enter the formula in a result cell, such as C2, and press Enter. To apply it to more rows, drag the fill handle at the lower-right corner of C2 downward. If a result appears as a number such as 45,000 rather than a calendar date, the calculation may still be correct; the cell likely needs date formatting.
How EDATE handles calendar dates
EDATE advances a date by a specified number of calendar months. It aims to keep the same day number in the destination month when that day exists. If it does not exist, Excel uses the last day of the destination month. For instance, adding one month to January 31 returns the last day of February, not a date in March.
The same rule matters when adding years, because the formula converts years to months. If A2 contains 29-Feb-2024, adding 12 months with =EDATE(A2,12) returns 28-Feb-2025. There is no February 29 in 2025, so EDATE uses the final day of February. Adding four years instead, with =EDATE(A2,48), returns 29-Feb-2028 because 2028 is a leap year.
EDATE can also move backward. A negative month count subtracts months, so with a date in A2 and a years-to-subtract value in B2, =EDATE(A2,-12*B2) moves back by that many years. Use a negative number only when subtraction is intended; otherwise, a negative value in the years cell can produce a result that looks unexpectedly early.
Enter and copy the formula step by step
- Put real Excel dates in one column, such as A2:A20. You can type dates using a format Excel recognizes in your regional settings, or create a date with the DATE function.
- Put the number of years to add in another column, such as B2:B20. Use whole numbers for whole-year additions.
- In C2, enter
=EDATE(A2,12*B2). This assumes the start date is in A2 and the year count is in B2. - Press Enter and inspect the result. If it displays as a serial number, select the result cells and apply a date format.
- Copy or fill the formula down. The relative references change by row, so C3 will refer to A3 and B3.
To use one fixed number of years for every row, place that number in B1 and lock its reference when copying. With each starting date in A2:A20 and the shared year count in B1, enter =EDATE(A2,12*$B$1) in C2. The dollar signs keep the reference on B1 as the formula is filled down, while A2 adjusts to A3, A4, and so on.
Leap years and end-of-month choices
There is no single rule for every leap-day situation; the appropriate result depends on what your dates represent. EDATE treats the date as a calendar anniversary and falls back to the last day of the target month when the original day is missing. This is often useful for renewal dates and schedules, but a policy or contract may define its own anniversary rule.
A different formula, =DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2)), keeps the original month and day components and asks DATE to construct a date in the new year. With 29-Feb-2024 in A2 and 1 in B2, it returns 1-Mar-2025. DATE normalizes an out-of-range day into the following month, so this is not the same outcome as EDATE, which returns 28-Feb-2025 in that example. Choose deliberately rather than assuming the formulas are interchangeable.
If the goal is always the last day of the month after adding years, use EOMONTH. With the start date in A2 and the number of years in B2, =EOMONTH(A2,12*B2) returns the final calendar day of the destination month. For example, five years after 15-Jun-2024, it returns 30-Jun-2029, not 15-Jun-2029. That is correct only when month-end is the desired result.
If you want to preserve month-end status only when the original date was itself month-end, use a conditional formula. With the date in A2 and years in B2, =IF(A2=EOMONTH(A2,0),EOMONTH(A2,12*B2),EDATE(A2,12*B2)) sends original month-end dates to the target month-end, while using EDATE for other dates. For example, 31-Jan-2024 plus one year becomes 31-Jan-2025, while 15-Jan-2024 plus one year becomes 15-Jan-2025.
Choosing between EDATE, DATE, and EOMONTH
For most requests to add a number of calendar years, EDATE multiplied by 12 is the clearest starting point. DATE and EOMONTH are useful when you specifically need their different date-building or month-end behavior.
- Use EDATE when you want the same calendar day where possible and the last day of the target month when it is not. This suits many recurring dates and anniversary calculations.
- Use DATE when you need to construct a date from a year, month, and day, or when the behavior of an overflowing day is intentional. Check leap-day results, because they can roll into March.
- Use EOMONTH when your target must be the last day of a month, regardless of the starting day.
- Use the conditional EOMONTH and EDATE approach when month-end dates should remain month-end but ordinary dates should retain their day number.
For periods that are not whole years, express the interval in months if that matches the requirement. For example, with a starting date in A2 and a number of months in B2, =EDATE(A2,B2) adds those months. If the request is instead for a precise number of days, add days directly; a year is not always the same number of days because leap years and calendar boundaries matter.
Check the date format and the source data
A date-looking cell may contain text rather than an Excel date value. EDATE needs a valid date serial or a date value Excel can interpret. Text imported from another system can be ambiguous: 04/05/2025 could mean April 5 or May 4 depending on the source convention and regional settings. Confirm the intended order before calculating.
To check the display, select the result cells and use Home > Number to choose a date format such as Short Date. For more control, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, choose the Number tab, and select Date or Custom. A custom format such as yyyy-mm-dd makes the displayed order unambiguous, although it does not change the underlying date value.
If Excel shows a number, that is usually a formatting issue. If it shows an error, check that the starting value is a valid date, that the year count is numeric, and that the formula uses commas between arguments in the standard English-language syntax shown here. Excel installations using other regional settings may display different date formats or use different formula separators. In Excel for the web, the same EDATE formula works; the ribbon layout can differ slightly from the Windows desktop app.
For imported data, convert text dates into actual dates before relying on results. Re-entering a date with a recognized format or using Excel’s data-conversion tools may be appropriate, but first verify the source convention. Merely applying a date format to text does not necessarily convert that text into a usable date.
Common mistakes and practical checks
- Adding years as days: A formula such as A2+365*B2 adds a fixed count of days. It can miss the calendar anniversary across leap years, so prefer EDATE for calendar-year additions.
- Forgetting the conversion to months: EDATE takes months, not years. Use 12*B2 for a year count, or enter a direct month count if that is what your data contains.
- Expecting every formula to treat 29 February alike: EDATE and DATE can return different results in a non-leap year. Decide whether February 28 or March 1 is appropriate for the use case.
- Using EOMONTH for an ordinary anniversary: EOMONTH always returns a month’s final day. It will change a date such as June 15 to June 30.
- Ignoring displayed precision: A custom format may hide information, but it does not alter the stored date. Check the formula bar or use a more revealing format when investigating results.
For a list of dates, test a few boundary cases before filling the formula across the entire dataset: a date near the end of a month, a leap-day date if one is present, and a date that crosses a year boundary. This small check can reveal whether the chosen rule matches the business meaning of the data.
Frequently Asked Questions
What formula adds 5 years to a date in Excel?
With the starting date in A2, use =EDATE(A2,60). Sixty months equals five years. To make the period adjustable, put the year count in B2 and use =EDATE(A2,12*B2).
How do I add years to a date and keep the same day?
Use =EDATE(A2,12*B2) with the start date in A2 and the years in B2. It keeps the day number when that day exists in the target month. If it does not, EDATE returns the target month’s last day, so a leap-day anniversary can become February 28.
Why does adding one year to February 29 return February 28?
The following year may not have a February 29. EDATE uses the last valid day of February in that case. If your intended rule is to roll the date into March, a DATE-based formula may produce that result, but verify that this convention is suitable for your calculation.
Can I add years directly with a plus sign?
Not as a calendar-year operation. Adding 365 days with a plus sign counts days and can miss the intended anniversary because years have different lengths. Use EDATE to move by calendar months, or use DATE when you need its specific year-month-day behavior.
Why is my result a large number?
Excel stores dates as serial numbers, and a cell using General format can display the serial rather than a calendar date. Apply a date format using Home > Number or Format Cells. If formatting does not help, check whether the input is a real date rather than text.
Can I subtract years with EDATE?
Yes. With the date in A2 and the number of years to subtract in B2, use =EDATE(A2,-12*B2). The negative month count moves backward; month-end and leap-day behavior follows EDATE’s date rules.
Related
- Data Validation and Quality Assurance in Excel
- Power Query Remove Leading Zeros Without Losing IDs
- Power Query Reference: What It Does and When to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel VLOOKUP Column Index Number ExplainedIn Excel VLOOKUP, the column index number tells Excel which column to return a value from, counting from…
- Freeze Values in Excel: Turn Formulas Into Fixed ResultsTo freeze values in Excel, select the formula cells, copy them, then choose Home > Paste > Paste…
- Excel IF Formula Examples: Tests, Logic, and ErrorsExcel’s IF formula checks whether a condition is true or false and returns one result for each outcome.…
- Excel Letter to Number: Convert Column Labels in FormulasTo convert an Excel column letter to its number, place the label in A2 and enter =COLUMN(INDIRECT(UPPER(A2)&"1")) in…
- Count Excel Without Duplicates: Formulas That WorkTo count distinct, nonblank values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0), replacing A2:A100 with your data range.…
- AVERAGEIF in Excel: Average Numbers by Text CriteriaYes. In Excel, AVERAGEIF can use a text criterion—such as a department name or status—to select which rows…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.