17

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.

2
  • 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, 2010 at 15:18
  • 1
    Are you certain the dates are off by four years; and not off by four years and one day? Jan 10, 2015 at 11:59

9 Answers 9

19

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!

2
  • Thanks everyone for the suggestions. This is the method I decided to use and it worked perfectly. Thanks!!
    – user30752
    Sep 22, 2010 at 15:38
  • Boy, this answer looks familiar... lol
    – Sux2Lose
    Sep 22, 2010 at 20:04
6
  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
3

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.

1

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.

0

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.

1
  • This will not cater for leap years
    – Linker3000
    Sep 22, 2010 at 15:33
0

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.

0

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.

1
  • 1
    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, 2015 at 16:51
0

Excel is frustrating - the only recommendation that worked for me was inserting a new column and dragging. And if any date is repeated in the column, you have to re-drag those one by one. I really still need the month days to move one up as well, but obviously I will have to update those manually. I hate the "number" tab. It's very confusing and untrustworthy.

0

Super easy way: Highlight the column you want to change. rt click and set date format to something useful, such as YYYY/MM/DD. Hit ctl-c. Create a new .txt file and open it. Hit ctl-v. Select the edit dropdown, then the replace function. Input your desired change and replace all. Hit ctl-a, then ctl-c. Return to your excel spreadsheet and highlight the column in question. Hit ctl-v, et voila.

1
  • Avoid posting answers to old questions that already have well received answers unless you have something substantial and new to add.
    – Toto
    Jan 3, 2023 at 18:23

You must log in to answer this question.

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