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

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

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)

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

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 *!!*

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

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 *!!*