Help sorting dates in Excel

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

Hi group,

I have an Excel spreadsheet. One of the columns is "Date" and all of the
data is in the format "April, 2002" or "December, 2004" etc. I am trying to
sort on this field, but Excel sorts alphabetically, putting December before
February, etc. I have tried to edit the format of the date field to make
it all numbers (to help with the sort), but I'm not finding the right one.

Any tips on how to sort this?

Thanks!
Aaron
 
Hi
you have to store the values as real date values in Excel. Of yourse
you can apply a custom format for these date like
DD MMMM, YYYY

Excel will sort real date values in a correct way
 
Thanks Frank. Is there anyway to do a mass change? Or would I have to do
each change by hand?

Aaron
 
"March, 2002"
"April, 2004"
"January, 2001"

I've started to go thru and do a find and replace. I have about 4 years,
and with 12 months each year, that's only 48 find and replace's. :)

I think that should do it. Thanks again.

Aaron
 
Hi
a formula approach in a separate cell:
=DATEVALUE("1-" & TRIM(LEFT(A1,FIND(",",A1)-1)) & "-" &
TRIM(MID(FIND(",",A1)+1,10)))
format this cell as date or with the custom format
MMMM, YYYY
and copy down for all rows. after this select this column, copy it and
paste it with 'Edit - Paste Special - Values' to remove the formulas.
After this you may remove the old column of data
 
Frank,

Just for the heck of it, I did Edit>Replace to remove the commas and voila, the
cells were correctly date formatted. Didn't even have to do the Copy>Paste
Special>Add routine. Confused :)

Best regards and keep up the good and inspiring work
Anders Silven
 
One more way, if it's ok to convert them to the first of the month.

Select your range of dates
Edit|Replace
replace , (single comma)
with _1, (space bar, 1, comma--I used underscore for the spacebar)

replace all.

March, 2002
will become
March 1, 2002

And excel will see it as a date.

Give that range the format you like when you're done.
 
Maybe even simpler is:

<Data> <TextToColumns> <Next> <Next>
Click in "Date", and choose"MYD", <Finish>.

--


Regards,

RD
 
Ahhhh you Excel amateurs....

This is done simply by doing the following:

- Change the format of the cell to the date you want, best i
yyyy/mm/dd for sorting purposes
- Do a replace on the cells - any replace will do for example, chang
"/" in 01/01/2004 to "-". The outcome will be a properly formatte
list.

Please do not bother the Excel King with these petty issues. My tim
is spent dealing with more complex Excel issues, such as using a tabl
containing undefined thesaurus words to disprove Einsteins Theory o
Relativity and return Sir Isaac newton to the true platform o
prominence in modern physics.

-The Excel Kin
 
Back
Top