In Excel, change only the year component of a date

calendar_today Asked Dec 10, 2010

thumb_up 10 upvotes

history Updated April 16, 2026

Question posted 2010 · +3 upvotes

How do I change just the year component of a date using Excel formulas?

example: 12/31/07 –> 12/31/11

Accepted answer +10 upvotes

Use (for example)

=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1))

Top `excel` Q&A (6)

Shortcut to Apply a Formula to an Entire Column in Excel +335 (2011)
How should I escape commas and speech marks in CSV files so they work in Excel? +136 (2012)
Convert xlsx to csv in linux command line +96 (2012)
How to create a link inside a cell using EPPlus +50 (2011)
IF statement: how to leave cell blank if condition is false ("" does not work) +44 (2013)
T-SQL: Export to new Excel file +44 (2012)

+10 upvotes ranks this answer #78 out of 167 excel solutions on this site .