Brewster Gillett wrote:
I'm attaching a stripped-down 200 rows or so of it as a sample. 200 rows gives a fair range of the dates, and of course displays theevidence that the column is *not* being sorted by date, but as text numerical data :-)It's only 22K, so shouldn't have any difficulty getting through... ...thanks for your efforts. BrewsterOn Thu, 2010-09-23 at 13:16 -0400, Richard Detwiler wrote:Brewster: I opened your file, went to cell D2, and entered the formula =DATEVALUE(C2). It gave me an integer number, 40198, which I knew was encouraging. Then when I formatted the cell as a date (choosing the 12/31/99 option), it gave me the date 01/20/99, which is what it should be.Then it's a simple matter of copying cell D2 down through as many rows as you need.bg: It all works just as you describe, but with a terminal flaw; once having done all that, and admiring the shiny new date-formatted column, absent the apostrophes, I essay to perform an ascending-order sort on it. Alas, Evo's sort is apparently *not* reading it as a real live date, because instead of parsing it as YY (or YYYY - I tried it both ways)then MM then DD, the sort function is reading the two leading digits, and ignoring the rest. This of course is exactly what the sort function was doing when the column still was in text rather than date format :-( It would appear, maybe, that the sort function is lacking an appreciation of the distinction between a straight text number and a date. Either that, or the "FORMAT,CELL" process produces something that has the appearance of a "date", but not the reality ?? Appreciate all the help - I am running out of ideas. I will go try some of the other combinations that have been suggested.
I'm not sure what you mean by Evo's sort. When I do this, everything sorts fine. See attached file.To explain something about dates, which might help grasping the situation, dates are stored as numbers, with 12/31/1899 being 1, 1/1/1900 being 2, 1/1/2000 being 36526, etc. All the formating does is take those numbers and put them into a date that humans can recognize as a date. You can choose multiple ways of displaying the date, but it doesn't change the underlying number. It is the underlying number that is used in sorting.
So I'm not sure why, when you convert the text dates into real dates that Calc can use, things don't sort. Maybe you should send your file again, this time after you've converted the text dates to "real" dates.
sampleCalcOOusersRD.ods
Description: application/vnd.oasis.opendocument.spreadsheet
--------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@openoffice.org For additional commands, e-mail: users-h...@openoffice.org
