Sorting malfunctioning
 

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


--
aposatsk
------------------------------------------------------------------------
aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709
View this thread: http://www.excelforum.com/showthread...hreadid=564413

Sorting malfunctioning
 

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:


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


--
aposatsk
------------------------------------------------------------------------
aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709
View this thread: http://www.excelforum.com/showthread...hreadid=564413

Sorting malfunctioning
 

On Mon, 24 Jul 2006 19:37:48 +0100, aposatsk
wrote:

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


--
Steve (3)

Sorting malfunctioning
 

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 format
but others with double-digit days _do_not_ change *!!*


--
aposatsk
------------------------------------------------------------------------
aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709
View this thread: http://www.excelforum.com/showthread...hreadid=564413

Sorting malfunctioning
 

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

Try DataText to ColumnsNextNextColumn Data FormatDateDMY and Finish.


Gord Dibben MS Excel MVP

On Mon, 24 Jul 2006 15:18:27 -0400, aposatsk
wrote:


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 format
but others with double-digit days _do_not_ change *!!*

Sorting malfunctioning
 

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.

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

Try DataText to ColumnsNextNextColumn Data FormatDateDMY and
Finish.


Gord Dibben MS Excel MVP

On Mon, 24 Jul 2006 15:18:27 -0400, aposatsk
wrote:


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
format
but others with double-digit days _do_not_ change *!!*


--
aposatsk
------------------------------------------------------------------------
aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709
View this thread: http://www.excelforum.com/showthread...hreadid=564413

Sorting malfunctioning
 

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


On Tue, 25 Jul 2006 13:44:52 +0100, aposatsk
wrote:


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.

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

Try DataText to ColumnsNextNextColumn Data FormatDateDMY and
Finish.


Gord Dibben MS Excel MVP

On Mon, 24 Jul 2006 15:18:27 -0400, aposatsk
wrote:


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
format
but others with double-digit days _do_not_ change *!!*

Sorting malfunctioning
 

SteveW Wrote:
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!


--
aposatsk
------------------------------------------------------------------------
aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709
View this thread: http://www.excelforum.com/showthread...hreadid=564413

Sorting malfunctioning
 

On Tue, 25 Jul 2006 16:10:13 +0100, aposatsk
wrote:


SteveW Wrote:
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!

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)

Sorting malfunctioning
 

SteveW Wrote:
On Tue, 25 Jul 2006 16:10:13 +0100, aposatsk =

wrote:


SteveW Wrote:
Sorry, of course !

MDY - during the text to columns conversion is defining the input
dat=
a,
=3D
=3D

yours need to be DMY

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

=3D

using.
In your case M/DD/YYYY

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)



-- =

Steve (3)

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


--
aposatsk
------------------------------------------------------------------------
aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709
View this thread: http://www.excelforum.com/showthread...hreadid=564413

Sorting malfunctioning
 

On Tue, 25 Jul 2006 21:05:01 +0100, aposatsk
wrote:


SteveW Wrote:
On Tue, 25 Jul 2006 16:10:13 +0100, aposatsk =

wrote:


SteveW Wrote:
Sorry, of course !

MDY - during the text to columns conversion is defining the input
dat=
a,
=3D
=3D

yours need to be DMY

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

=3D

using.
In your case M/DD/YYYY

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)



-- =

Steve (3)

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 :)


--
Steve (3)