I wrongly set the date of 2000 cells. I want to add 20 years of those cells. How?

Question

My first thought was to

1. add a duplicate cell and show the formula as general number.
2. add another cell that adds 20 years worth of days.
3. change the third cell formula back to date.

But I feel like there's way to directly modify the first cell(the original and the wrong).

How do I do this?

Answer 1

Try using the EDATE() formula. Since it adds months you'll need 240. Something like this:

=ARRAYFORMULA(EDATE(A2:A2000,240)) 

Then paste values.

Alternatively, you could use this formula:

=ARRAYFORMULA(DATE(YEAR(A2:A2000)+20,MONTH(A2:A2000),DAY(A2:A2000)))

Answer 2

IIRC, this does work in Excel with a special paste function. Sadly, Google Sheets does not have it, so a simpler request for just adding 1 to a couple of cells doesn't work without the trick you describe. I recommended that procedure in one of my earlier answers.