Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:




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
Are you certain the dates are off by four years; and not off by four years and one day? – Ron Rosenfeld Jan 10 '15 at 11:59
up vote 11 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

If you have a fixed year in mind:


if you want to shave off a number of years:


Where A1 is the cell containing the date to convert.

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

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.

share|improve this answer

The Replace function under the Find & Select tab can change a year throughout the entire Workbook. By selecting Options >> Within: Workbook it will change all years to the year you specify.

Reminder: For leap years you will have to manually go in and remove the 29th day if you're converting from 2016 to 2017 etc.

share|improve this answer
This is a duplicate of several previous answers. Although you wouldn't notice from some of the previous answers, the intention is that each answer contribute another solution. – fixer1234 Dec 3 '15 at 16:51

You must log in to answer this question.

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