Sort by date problem

[scellis]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

I have a 4021 row x 37 column worksheet (Excel 2008, version 12.1.5) that refuses to sort any of several date columns properly.I suspect that the worksheet is maintaining those date columns as text, because I am also unable to change the date format from the 3/14/2001 format to any other style and everything is sorted according to the left-most numerals (months) rather than by the year. Sheet and workbook protection are off. It's been a few years since I've played in the Excel world... thanks for your help!

 

[macropod]

Hi,

You could use a formula like:
=IF(ISERROR(DATEVALUE(A1)),A1,DATEVALUE(A1))
to convert any 'text' dates in A1 to a date value, which you can then re-format as a date.

If you put this formula into a cell and point it to the first date in your column, then copy down as far as needed, you'll then have
a column of date values. You could then copy & paste these (Paste Special|Values) back into the original column, You might need to
use Paste Special|Formats if any of the pasted dates appear as a serial number. Once you've done that, you should be able to sort to
your heart's content.

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

I have a 4021 row x 37 column worksheet (Excel 2008, version 12.1.5) that
refuses to sort any of several date columns properly.I suspect that the
worksheet is maintaining those date columns as text, because I am also unable
to change the date format from the 3/14/2001 format to any other style and
everything is sorted according to the left-most numerals (months) rather than
by the year. Sheet and workbook protection are off. It's been a few years
since I've played in the Excel world... thanks for your help!

One way:

Select the column with teh Text dates. Choose Data/Text to Columns.
Click Next, Next. Choose MDY from the Date dropdown. Click Finish.

 

[scellis]

Problem solved!

Thanks to macropod and J. E. McGimpsey -- both your solutions worked. I tried them on different columns of data and each worked perfectly. Now I can get back to work on this project.

Thanks again!