2

I have a date in cell D3 AND THEN IN CELL C2 I HAVE THIS FORMULA:

=DATE(YEAR(D3)+1,MONTH(D3),DAY(D3)-1)

Which adds the date for review a year on. but when I need to insert the cell to the right to add a new date it changes the formula to cell E3 and i want it to stay in D3 and calculate from the new date. i have tried indirect function but i cant get it to work with the date format

Please help

Improve this question
1
  • You could try using indirect
    – Dave
    Feb 8, 2016 at 15:22

1 Answer 1

Reset to default
1

You can use both OFFSET or INDIRECT:

  • =DATE(YEAR(INDIRECT("D3",TRUE))+1,MONTH(INDIRECT("D3",TRUE)),DAY(INDIRECT("D3",TRUE))-1)
  • =DATE(YEAR(OFFSET(C2,1,1))+1,MONTH(OFFSET(C2,1,1)),DAY(OFFSET(C2,1,1))-1)
Improve this answer
2
  • Thank you I will try this when I'm back in work tomorrow. I really appriciate you taking the time to answer my problem.
    – Sarah-Jane
    Feb 8, 2016 at 19:42
  • +1 for the OFFSET() option, which will still maintain functionality if the sheet is modified in a row above or column to the left of C2
    – Code Jockey
    Feb 8, 2016 at 20:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .