How to Add Years to a Date

If you want to add years to a date in excel, you can’t just simply do an addition to add years. If you add 1 year as a number to a date, it wil add 1 day. In excel, 1 is equal to 1 day (24 hours). If you add multiples of 365 to a given date then it will give an incorrect answer, since there can be a leap year in between. To add years to a date in excel, we use a formula.

Generic Formula

=DATE(YEAR(date)+num_years, MONTH(date),DAY(date))

Date: the date in which you want to add years.
Num_years: the number of years you want to add.
Example: Add 5 Years to Start Date.
Here we have some dates. In each date we need to add five years.
0034
Write this formula in cell B2 and copy it down the rows.

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

This returns a date 5 years ahead of start date.
0035
How it works?
It’s simple. We extracted year, month and date from date using function of same name. Added 5 to years YEAR(A2)+5. And then integrated year, month and date using DATE function of excel. This gave us a new date of 5 years ahead.
How to subtract years from date?
Just replace the addition sign with subtraction sign (“-”) subtract years from a date.

=DATE(YEAR(A2)-5,MONTH(A2),DAY(A2))

Above formula will give you a date exact 5 years before.

So yeah guys, this how you can add years to a date in excel easily. In example I have used hardcoded years. You can also give reference of cell so that it can be dynamic. Let me know if you have any query regarding this or any other topic of Excel and VBA.

Related Articles:
Add Months to a Date
Calculate years between dates
Calculate days, years and months from certain date
Calculate age from date of birth
Calculate Minutes Between Dates & Time In Microsoft Excel
Calculate Months Between dates in Excel

Popular Articles:
The VLOOKUP Function in Excel
COUNTIF in Excel 2016
How to Use SUMIF Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube