column not sorting correctly by date

[Joe]

I'm trying to sort a spreadsheet by a column that begins with a date
but has other information in the cell. When I sort by ascending it
begins with the cells that don't have any dates preceding the
alphabetical content then it will start with "4/6/2008 then 4/2/2008
then 4/10/2008, then it works it's way down to the March dates
correctly. Is there any way I can get it to straighten out April so
the most recent dates sort from the top? I checked to see if there
were any spaces before the dates and there weren't. Is there anything
else I should check for that would make it sort out of order?

 

[Mike H]

Post a sample of your data unsorted and what you would like to see.

Mike

 

[Joe]

when I sort by descending this is what I get:


4/8/2008xxxxx
4/8/2008 xxxxxxxxxx
4/8/2008 xxxxxxxxxx
4/8/2008 xxxxxxxxxx
4/8/2008 - xxxx
4/8/2008 - n/axxxx
4/8/08 xxxxxx
4/8/08 11:48 amxxx
4/4/2008 wants xxxxxx
4/4/2008 - xxxxx
4/3/2008 xxxxxx
4/3/2008 - xxxxxx
4/2/2008 - xxxx
4/2/2008 - xx xxx
4/11/2008 xxxxxxx
4/11/2008 xx xxxx
4/10/2008 xxxxx
4/10/2008 xxxxx
4/10/08 -xx xxx
4/10/08 2:32 pm xxxxxxxxxxxx
4/10/08 12:39 pm -xxxxx
4/10/08 1:27 pm XXXXXX
4/1/2008 xxxxxxxxxxxxxxxx
4/1/2008 xxxxxxxxxxxxxxxx
4/1/2008 xxxxxxxxxxxxxxxx

 

[Earl Kiosterud]

Joe,

If you have stuff in addition to a date in a cell, then it's text, not a number, and it'll
sort alphanumerically, not in date sequence. You've made your job difficult by combining
the date with other stuff. And your dates don't have a consistent character count (you
didn't use 04/06/2008). Try this in a helper column. It presumes a space after the date.
Format it for a date format (Format - Cells - Number - Date). Sort on that column:

=DATEVALUE(LEFT(A2,SEARCH(" ",A2,1)-1))

 

[GoBow777]

when I sort by descending this is what I get:


4/8/2008xxxxx
4/8/2008 xxxxxxxxxx
4/8/2008 xxxxxxxxxx
4/8/2008 xxxxxxxxxx
4/8/2008 - xxxx
4/8/2008 - n/axxxx
4/8/08 xxxxxx
4/8/08 11:48 amxxx
4/4/2008 wants xxxxxx
4/4/2008 - xxxxx
4/3/2008 xxxxxx
4/3/2008 - xxxxxx
4/2/2008 - xxxx
4/2/2008 - xx xxx
4/11/2008 xxxxxxx
4/11/2008 xx xxxx
4/10/2008 xxxxx
4/10/2008 xxxxx
4/10/08 -xx xxx
4/10/08 2:32 pm xxxxxxxxxxxx
4/10/08 12:39 pm -xxxxx
4/10/08 1:27 pm XXXXXX
4/1/2008 xxxxxxxxxxxxxxxx
4/1/2008 xxxxxxxxxxxxxxxx
4/1/2008 xxxxxxxxxxxxxxxx

Joe:

Any number combined with a character other than a number i
automatically converted to text and Excel will sort it as text and no
as a number.

Type "Default sort orders" in Excel's help box