Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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

5 Answers 5

up vote 9 down vote accepted

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!

share|improve this answer
    
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
  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
share|improve this answer

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.

share|improve this answer

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)

alt text

note: C3 is the highlighted cell.

share|improve this answer
    
This will not cater for leap years –  Linker3000 Sep 22 '10 at 15:33

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.