sorting by date

B

bubba1965

I am having trouble sorting a table by date. When I choose to sort th
column by date - it sorts it alphabetically by date - i.e. with Augus
coming first.

How can I have Excel sort chronologically, when the date include
month, day, yea
 
M

Michael Malinsky

It sounds as if the dates are formatted as text. You can change this to an
Excel date value by inserting a helper column and using the following
formula (assuming your dates are in column A:

=DATEVALUE(A1)

and copying down. Excel will then recognize the values as date values, not
text, and will sort chronologically.

HTH

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winne
the Pooh
 
B

bubba1965

I guess I must be doing something wrong.

I inserted a column to the right of column A
Inserted the formula into B1 and then copied down.
I then tried to sort by column A and I did not get the desired results


What am I doing wrong
 
D

Dave Peterson

Excel isn't seeing your value as a date. In fact, xl can't convert it from your
text to a real date (using Michael's suggestion).

What do your dates look like?

If you post all(?) the variations, maybe someone can come up with a formula to
convert them to real dates.
 
B

bubba1965

Thank you for any help you can provide. My dates look like this

Sep. 4, 1999
Oct. 10, 1971
Oct. 24, 1971
Oct. 2, 1972
Oct. 14, 1972
Oct. 8, 1973
Sep. 27, 1975
Oct. 4, 1976
Oct. 23, 1976
Sep. 24, 1977
Sep. 26, 1977
Oct. 19, 1977
Sep. 13, 1978
Sep. 23, 1978
Sep. 12, 1979
Sep. 14, 1979
Sep. 24, 1980
Sep. 4, 1985
Sep. 25, 1993
Sep. 2, 1994
Sep. 29, 1995
Oct. 11, 1995
Aug. 30, 1996
Nov. 5, 1996
Aug. 29, 1997
Oct. 8, 1997
Sep. 29, 1998
Oct. 18, 2000
Aug. 28, 2001
Aug. 31, 2001
Sep. 17, 2002
Sep. 27, 1971
Oct. 3, 1971
Oct. 4, 1974
Oct. 29, 1974
Sep. 6, 2003
 
O

On Film

Highlight dates
DATA > TEXT TO COLUMN
Fixed width
Remove column breaks
Select DATE MDY
Finish
Then it will sort. I tried with pasting from your email into Excel.
The sort will work

Mike
 
D

Dave Peterson

Another way.

Select your range of dates
Edit|Replace
Find what . (period)
replace with (leave blank)

Excel saw them as dates for me. I reformatted to the way I like them.
 
Top