Sorting malfunctioning

  • Thread starter Thread starter aposatsk
  • Start date Start date
A

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
 
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:
 
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
Click to expand...


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

Check that your cells are not set to text, re-format them as dates
 
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 *!!
 
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
 
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.
 
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
 
SteveW said:
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
Click to expand...

I don't understand. If i choose DMY, 31/3/2005 changes to 3/31/2005!
 
I don't understand. If i choose DMY, 31/3/2005 changes to 3/31/2005!
Click to expand...
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)
 
SteveW said:
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)
Click to expand...

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

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