change a list of 2 digit years to 4 digit years?

[Guest]

I need to convert a list of dates that is formatted with 2 digit years, 89
for 1989 or 02 for 2002, to 4 digit years. The list has about 6,000 dates,
so I REALLY don't want to fix it one at a time. Excel seems to know what to
do, it I simply double click the date and then move off it (by click or enter
key) the date is adjusted to 4 years accroding the microsoft formula (which
works for ALL the dates in the list). How do I have excel do all the dates
at once?

 

[Guest]

Select a single, empty cell
Edit->Copy
Select all the dates
Edit->Paste Special->Add->Values

Should take care of it

 

[Pete_UK]

If they are proper Excel dates then all you need to do is highlight
the column by clicking on the identifying letter at the top of the
column, then Format | Cells | Number tab | Custom ... and enter dd/mm/
yyyy (or possibly mm/dd/yyyy depending on your preference) in the
panel and click OK.

Hope this helps.

Pete

 

[Guest]

That didn't do it, it just reduced the date to a set of 6 numbers, 29-Oct-06
turns into 102906. I should maybe add that Excel sees all of these dates
errors. Each cell has a little green triangle in the upper left corner,
mousing over the cell brings up a little notification icon with a drop down
menu that gives several options for fix fixing or ignoring the error. I can
change a block of cells all at once this way, but the dates are mixed, so is
takes a while to select them all.

 

[Peo Sjoblom]

First, make a backup copy of your file

Format column as dd-mmm-yyyy, then select the column, do data>text to
columns, click next twice, under column data format select date and from
dropdown pick MDY, click finish. If necessary reformat as dd-mmm-yyyy again


--


Regards,


Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

Try this... select the entire column, click Data/Text To Columns form
Excel's menu bar, click Next and Next again on the dialog box that appears,
select the Date option button from "Column data format" section (make sure
the format field shows the month, day, year order you want) and then click
Finish.

Rick