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.