How to add one or more months to a date; how to add or substract one or more years to a date

Sometimes we need to add 1 or 2 months to a date or one year to a date. What is the best way to do this.

Some will say it is to add 31 days to the date or to add 365.25 days to one year. But this is not accurate as the months and years are not always the same.

= A1 + 365.25 (this would add about one year to the date but will not be exact)

Here is the trick.

By combining the DATE(), Year(), Month() and Day() formula you get the trick and adding or substracting one year or one month to a date will work fine.

To add three (3) months to the cell A1

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

To add 2 years to the cell in A1

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

You can also substract a month to a date

 =DATE(YEAR(A1), MONTH(A1) - 2 , DAY(A1))

Or you can substract a few years to a date (although you will not become younger that way :-))

 =DATE(YEAR(A1) - 10, MONTH(A1) , DAY(A1))


This should keep the count correct and will let you add a month or a year or even days easily in Excel.

Remark: Just be careful when copying from this page the add one year formula, your Excel version might want to have semicolumn (;) instead of commas (,) as separators.


Please Tweet, Like or Share us if you enjoyed.