# Change year in many date fields in excel?

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.

-
Note: If I use the solution from the question I referenced, all the dates I already have for 2010 go back to 2006. So checking/unchecking that box doesn't solve this problem. –  user30752 Sep 22 '10 at 15:18
Are you certain the dates are off by four years; and not off by four years and one day? –  Ron Rosenfeld Jan 10 at 11:59

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!

-
Thanks everyone for the suggestions. This is the method I decided to use and it worked perfectly. Thanks!! –  user30752 Sep 22 '10 at 15:38
Boy, this answer looks familiar... lol –  Sux2Lose Sep 22 '10 at 20:04

If you have a fixed year in mind:

``````=DATE(2010,MONTH(A1),DAY(A1))
``````

if you want to shave off a number of years:

``````=DATE(YEAR(A1)-4,MONTH(A1),DAY(A1))
``````

Where A1 is the cell containing the date to convert.

-

If the data is all collected in a column or row, you can take the data and subtract 1460 (the ammount of day in 4 years)

note: C3 is the highlighted cell.

-
This will not cater for leap years –  Linker3000 Sep 22 '10 at 15:33
1. Highlight the column where the dates are, then under the Home Tab, go to "Find & Select", then click "Replace"
2. Type "2014" under Find What, then type "2010" under Replace With
3. Click Replace All
-

if you are in a Mac go to the menu bar under Edit go to Replace and put the values you wanna replace, press replace all.

-

On the HOME TAB under NUMBER change the date to LONG DATE rather than SHORT DATE this allows you to go into FIND & SELECT and REPLACE ALL the unwanted dates.

-