| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 *!!* |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 *!!* |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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) |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
| Sorting Sorting Sorting | Excel Discussion (Misc queries) | |||
| sorting non contiguous ranges | Excel Discussion (Misc queries) | |||
| Advice sought: Multiple column sorting | Excel Worksheet Functions | |||
| sorting number in ascending order | Excel Discussion (Misc queries) | |||
Linear Mode
