Consider the following dataset. We’ll add years to the joining dates.
Method 1 – Using Simple Arithmetic to Add Years to a Date in Excel
- We modified the dataset to contain the number of years to add in cell C4 and moved the rest down.
- Select the D7 cell.
- Insert the following formula.
=C7+($C$4*365)
This will add the entered number of years (In my case, 2 years) to the existing date by adding 365 days times the value of C4.
- Hit Enter.
- Use the Fill Handle tool and drag it down from the D7 cell to the D11 cell.
- Here’s the result.
Read More: How to Add 2 Years to a Date in Excel
Method 2 – Inserting the EDATE Function to Add Years to a Date
Syntax of the EDATE Function
=EDATE (start_date, months)
- Select the D7 cell.
- Insert the following formula:
=EDATE(C7,($C$4*12))
- Hit Enter.
- Use the Fill Handle tool and drag it down from the D7 cell to the D11 cell.
- Here are the results.
Read More: How to Add 3 Years to a Date in Excel
Method 3 – Combining Multiple Functions to Add Years to a Date in Excel
Syntax of the DATE Function
=DATE (year, month, day)
- Choose the D7 cell.
- Insert the following formula.
=DATE(YEAR(C7)+$C$4,MONTH(C7),DAY(C7))
- Hit Enter.
Formula Breakdown
DAY(C7): This argument in the DATE function shows the number of days for the date and the value is 1. MONTH(C7): This argument in the DATE function finds the number of months for the date and it returns the value 1. YEAR(C7)+$C$4: This argument in the DATE function shows the number of years for the date and it returns the value by adding the value of C4 cell (5) as 2023. =DATE(YEAR(C7)+$C$4,MONTH(C7),DAY(C7)): This whole function finally shows the result as 1/1/2023.- Use the Fill Handle tool and drag it down from the D7 cell to the D11 cell.
- Here’s the result.
Read More: How to Create a Formula in Excel to Change Date by 1 Year
Download the Practice Workbook
Related Articles
- How Do I Add 7 Days to a Date in Excel
- How to Add 30 Days to a Date in Excel
- How to Add Days to a Date in Excel Excluding Weekends
- How to Add Weeks to a Date in Excel
- How to Add Months to Date in Excel
- How to Add 3 Months to a Date in Excel
- How to Add 6 Months to a Date in Excel
<< Go Back to Adding Days to Date | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Method 1 is very simple and works great but, one issue is it doesn’t take into consideration leap years, so for anything over 4 years (plus depending no the year you start with) it’ll be off by 1 every 4 years.
Hello Cire,
Thank you for your feedback! You’re correct—simply adding years by multiplying 365 days doesn’t account for leap years, which can make the result inaccurate over longer periods.
To accurately add years to a date (including leap years), you should use the EDATE or DATE function in Excel:
Use EDATE:
If your date is in cell A2 and you want to add 4 years:
=EDATE(A2, 12*4)
This will add exactly 4 years, taking leap years into account.
Use DATE:
If your date is in cell A2 and you want to add 4 years:
=DATE(YEAR(A2)+4, MONTH(A2), DAY(A2))
This also accurately adds years, even if there are leap years.
These methods are more reliable for date calculations over several years.
Regards
ExcelDemy