So I've noticed in a worksheet I have many dates that are four years off, no doubt because of improper copy/paste from earlier question.
Is there an easy way to convert a bunch of dates I select to a specific year, but keep the month and day the same? For example, I'd like to quickly convert these cells:
6/1/2014
6/5/2014
7/18/2014
to
6/1/2010
6/5/2010
7/18/2010
There are hundreds of these, so I'd rather not do it manually.
Best Answer
If the date cells are all in one column, here's a quick and dirty way:
Assuming the dates are in A1 downwards, insert two columns to the right.
In B1, put the formula:
=DATE(YEAR(A1)-4,MONTH(A1), DAY(A1))Copy this formula down the column to recalculate all the dates from column A.
Now select and 'copy' column B (the new dates) and use 'paste as values/paste values' into column C.
Now delete the original column and the one containing the formulas to leave the new fixed dates.
If the original dates are not in neat columns you may have to do a bit more work!