Sorting malfunctioning

[aposatsk]

A column contains a list of dates in the form: day/month/year.
If the list contains all single-cell days, then sorting in descending
order works fine. However, if the list contains a date such as
24/4/2005 as opposed to 2/4/2005, the date 24/4/2005 always appears
first on the list, _above_ an earlier date such as 1/1/2006!

For example, here is a "sorted" list:

24/2/2005
6/3/2006
3/4/2006
3/4/2006
3/4/2006
3/4/2006
3/4/2006
1/4/2005
8/4/2004

As i said, when there are only single-digit days, the sorting works
fine:

6/3/2006
3/4/2006
3/4/2006
3/4/2006
3/4/2006
3/4/2006
1/4/2005
8/4/2004
8/4/2004
8/4/2004
8/4/2004
8/4/2004

 

[Guest]

Hi,

did you sure that it's a date? it looks like a text.

try to format as dd/mm/yyyy

hth
regards from Brazil
Marcelo



"aposatsk" escreveu:

 

[SteveW]

24/2/2005
6/3/2006
3/4/2006
3/4/2006
3/4/2006
3/4/2006
3/4/2006
1/4/2005
8/4/2004

works fine for ne, cut/paste from your list.

Check that your cells are not set to text, re-format them as dates

 

[aposatsk]

The list is formatted as "Date". The exact format type is " *3/14/2001
. I am not sure what the * signifies.

If i change the type to, say, 3/14/01, some days convert to this forma
but others with double-digit days _do_not_ change *!!

 

[Gord Dibben]

If the date was entered as text, which I think it was, re-formatting will not
change it a date.

Try Data>Text to Columns>Next>Next>Column Data Format>Date>DMY and Finish.


Gord Dibben MS Excel MVP

 

[aposatsk]

Well we're getting closer but there are still problems. When i choose
MDY, dates such as 30/3/2005 *do not* change to 3/30/2005. But when i
choose DMY, 30/3/2005 *does* changes to 3/30/2005.

 

[SteveW]

Sorry, of course !

MDY - during the text to columns conversion is defining the input data,
yours need to be DMY

What you see afterwards depends on the locale/date style that you are
using.
In your case M/DD/YYYY

BTW you can't have a format type of "*3/14/01" or did you mean that was
the actual data ?

Steve

 

[aposatsk]

Sorry, of course !

MDY - during the text to columns conversion is defining the input data,
=
=

yours need to be DMY

What you see afterwards depends on the locale/date style that you are
=

using.
In your case M/DD/YYYY

I don't understand. If i choose DMY, 31/3/2005 changes to 3/31/2005!

 

[SteveW]

I don't understand. If i choose DMY, 31/3/2005 changes to 3/31/2005!

DMY treats 31/3/2005 as 31st March 2005

3/31/2005 is March 31st 2005

So all is correct.

Now if you want your dates in English, ie 31/3/2005
then this has nothing to do with the data nor the Text to Column
conversion.

You have to alter this at a Windows level (Control Panel, Regional
Settings)

 

[aposatsk]

DMY TREATS 31/3/2005 AS 31ST MARCH 2005

3/31/2005 IS MARCH 31ST 2005

So all is correct.

Now if you want your dates in English, ie 31/3/2005
then this has nothing to do with the data nor the Text to Column =

conversion.

You have to alter this at a Windows level (Control Panel, Regional =

Settings)



-- =

Steve (3)

Yes but the problem is, DMY 31/3/2005 changes to 3/31/2005 ! 3/31/2005
is NOT DMY.

 

[SteveW]

Yes but the problem is, DMY 31/3/2005 changes to 3/31/2005 ! 3/31/2005
is NOT DMY.

Politley this is the last time I'll reply on this
Everything is in all the previous posts.

You have text in a cell
Text format is dd/m/yyyy
Convert using Text to COlumns, ensure you specifiy *text* data is a date
in the DMY format.

Convert.

Now Excel comes along and displays the numerical date in whatever format
you want.

Custom Format dddd dd mmmm yy gives Monday 30 September 2...

YOu either have a generic Date format with US style as your windows date
style
or you have put in a specific date format mm/d/yy - which is unlikely as
you
started this whole thing off because the dates where in text format