 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2006Sorting 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  Signatureaposatsk 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 > 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 [quoted text clipped - 29 lines] > 8/4/2004 > 8/4/2004 > 24/2/2005 > 6/3/2006 [quoted text clipped - 5 lines] > 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 SignatureSteve (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 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 >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 [quoted text clipped - 12 lines] > format > >but others with double-digit days _do_not_ change *!!* Signatureaposatsk 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 > 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 [quoted text clipped - 17 lines] >> format >> >but others with double-digit days _do_not_ change *!!* SteveW Wrote: > Sorry, of course ! > [quoted text clipped - 9 lines] > using. > In your case M/DD/YYYY I don't understand. If i choose DMY, 31/3/2005 changes to 3/31/2005! Signatureaposatsk > SteveW Wrote: >> Sorry, of course ! [quoted text clipped - 12 lines] > > 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) SignatureSteve (3) SteveW Wrote: > > SteveW Wrote: > >> Sorry, of course ! [quoted text clipped - 35 lines] > > Steve (3) Yes but the problem is, DMY 31/3/2005 changes to 3/31/2005 ! 3/31/2005 is NOT DMY. Signatureaposatsk > SteveW Wrote: >> [quoted text clipped - 40 lines] > 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 :) SignatureSteve (3) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.