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.
28 Answers
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- Highlight the column where the dates are, then under the Home Tab, go to "Find & Select", then click "Replace"
- Type "2014" under Find What, then type "2010" under Replace With
- Click Replace All
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.
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.
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.
1if 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.
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.
1Excel 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.
