Problem sorting by date after 2029 on Excel 2003

[wtchywmn9]

I have an inventory list of corporate bonds with maturities out to 2039.
When I sort by the date column, it puts anything with a maturity starting in
2030 or longer at the top of the list. It's reading it as 1930, rather than
2030.

I went into Control Panel and followed the direction on Microsoft's
website...link below...to change the range of dates that it recognizes to
2000 to 2099. But it doesn't seem to have worked. I tried totally shutting
down the computer to reset everything, but it doesn't make any difference.

http://office.microsoft.com/en-us/excel/HP100541411033.aspx#Learn about the two date systems

FYI...I change the format to MM/DD/YYYY when I open the file. Nothing
happens. The original info is in the MM/DD/YY format. And I select the
"Sort anything..." option when the box pops up during Sort. The other
alternative doesn't work at all.

Now here's the weird thing...

I did a search using "sorting by date in excel" and found a thread called
"Date Format Not Responding to MM/DD/YY", and it helped...a little.

http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

If I double-click on the cell with the date, then click on the cell next to
it, the date switches to the MM/DD/YYYY format, with the correct year. These
lists can have a few hundred entries, so the double click on each cell thing
is not practical.

If I follow the instrux from Sheeloo and use the Data/Text to Column
function, it pulls the year in as beginning with 19, rather than 20. I tried
different variations and nothing works.

Cyber-gremlins ????? Or is there a setting somewhere in Excel that needs to
also be tweaked to 2000 to 2099?

Thanks.

 

[Dave Peterson]

Once the value/date is entered, changing the control panel settings won't change
the value/date.

If you change the format for one of those offending cells and what's displayed
doesn't change, then the value in that (those?) cells aren't really dates.

They're just text that look like dates.

You could convert the text to dates (even though they're not the right century),
then use a formula in an adjacent column to fix that:

=DATE(YEAR(A1)+IF(YEAR(A1)<2000,100,0),MONTH(A1),DAY(A1))

drag it down the range and copy|paste values over the original column--and
delete the helper column of formulas.