Increment date by 12 months

  • Thread starter Thread starter Attila Fust
  • Start date Start date
A

Attila Fust

I would like to create a formula to increment a date in
one cell by 12 months in the cell below. For example, in
cell a1 the date is jan 1/2004. I want a formula to
automatically calculate the date in cell a2 to be jan
1/2005 (increase of twelve months).

In Access you can use DateAdd. Is there something I can
do in Excel?

Attila
 
Hi
try
=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))

note: this may give you a wrong result for 29-Feb-2004 in cell A1
 
Hi
to also adjust for leap years use
=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))-(DAY(A1)<>DAY(DATE(YEAR(A1)-1,MONTH
(A1),DAY(A1))))
 
That worked well. In order to get the leap years in the
subsequent rows I made the reference A1 absolute $A$1 and
incremented accordingling. ie in the 5th row the
increment was +5.

Thanks for your help.

Attila
 
Back
Top