I have two columns in a sheet, approx 1000 rows. A1:A1000 contain the
purchase date (i.e. 1/1/2000). I need to have B1:1000 display the date 3
years from the purchase date. I do not want to add this in manually for each
column, so I was hoping there was a way to do something like
=date(year(address(row(), column()-1, 4) +3, month(address(row(), column()-1,
4), day(address(row(), column()-1, 4)).

In my view, this looks at the year, month, and day for each cell that is in
the same row that formula is, and one column to the left. it adds 3 years,
and displays the same month and day value.

I think I'm close to getting it, but the function errors out.

If the date was 2/29/2008 what date should be returned after adding 3 years?

If the correct result should be 3/1/2011 use this:

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

If the correct result should be 2/28/2011 use this:

=EDATE(A1,36)

Format as Date

Note that the EDATE function requires the Analysis ToolPak Add-in in
versions of Excel prior to Excel 2007.

--
Biff
Microsoft Excel MVP


"rstasiunas" wrote in message
...
I have two columns in a sheet, approx 1000 rows. A1:A1000 contain the
purchase date (i.e. 1/1/2000). I need to have B1:1000 display the date 3
years from the purchase date. I do not want to add this in manually for
each
column, so I was hoping there was a way to do something like
=date(year(address(row(), column()-1, 4) +3, month(address(row(),
column()-1,
4), day(address(row(), column()-1, 4)).

In my view, this looks at the year, month, and day for each cell that is
in
the same row that formula is, and one column to the left. it adds 3
years,
and displays the same month and day value.

I think I'm close to getting it, but the function errors out.