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
<[email protected]> 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
<[email protected]> 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
<[email protected]> 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
<[email protected]> 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 =

<[email protected]> 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
>> <[email protected]> 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 *!!*
>
>

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

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

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

<[email protected]> 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)

Originally Posted by SteveW

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

<[email protected]> 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.

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

<[email protected]> wrote:

>
> SteveW Wrote:
>> On Tue, 25 Jul 2006 16:10:13 +0100, aposatsk =3D
>>
>> <[email protected]> wrote:
>>
>> >
>> > SteveW Wrote:
>> >> Sorry, of course !
>> >>
>> >> MDY - during the text to columns conversion is defining the input
>> dat=3D
>> a,
>> >> =3D3D
>> >> =3D3D
>> >>
>> >> yours need to be DMY
>> >>
>> >> What you see afterwards depends on the locale/date style that you
>> are=3D
>>
>> >> =3D3D
>> >>
>> >> 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 =3D=

>>
>> conversion.
>>
>> You have to alter this at a Windows level (Control Panel, Regional =3D=

>>
>> Settings)
>>
>>
>>
>> -- =3D
>>
>> 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)