Performing date functions after getting cell address
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. |
Performing date functions after getting cell address
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. |
All times are GMT +1. The time now is 03:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com