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

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
<aposatsk.2bgq8v_1153766413.609@excelforum-nospam.com> 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 !!

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

On Mon, 24 Jul 2006 15:18:27 -0400, aposatsk
<aposatsk.2bgs3j_1153768811.4868@excelforum-nospam.com> 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 *!!*

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

On Mon, 24 Jul 2006 15:18:27 -0400, aposatsk
<aposatsk.2bgs3j_1153768811.4868@excelforum-nospam.com> 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.

Originally Posted by 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

On Mon, 24 Jul 2006 15:18:27 -0400, aposatsk
<aposatsk.2bgs3j_1153768811.4868@excelforum-nospam.com> 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 *!!*

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 =

<aposatsk.2bi4h5_1153831513.767@excelforum-nospam.com> 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 Data>Text to Columns>Next>Next>Column Data Format>Date>DMY and
>> Finish.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Mon, 24 Jul 2006 15:18:27 -0400, aposatsk
>> <aposatsk.2bgs3j_1153768811.4868@excelforum-nospam.com> 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 *!!*
>
>