Won't Sort
Posted by junsport on July 11, 2001 7:16 AM
I'm trying to sort a list by date but not all of the list is properly sorted. Some of the dated are not sorted and are pushed to the bottom. They are all in the same format and i have tried copying and pasting just the values. What can i do to fix this?????
Posted by Mark W. on July 11, 2001 7:44 AM
Are they all the same data type -- text vs. numeric?
Expand the column width. Are some of the dates
left-aligned while others are right-aligned?
Posted by junsport on July 11, 2001 7:52 AM
All the text is the same type and they are all aligned to the left.
Posted by Mark W. on July 11, 2001 7:58 AM
> All the text is the same type...
How have you arrived at this conclusion? Did you
test it using the ISTEXT() function?
> ...they are all aligned to the left.
What horizontal alignment are you using -- General?
Use the Format | Cells... menu command to check?
Posted by junsport on July 11, 2001 8:29 AM
i did the ISTEXT() function and it is false for some and true for others. I guess that some of it is in test form then. Hoe can i Change it all to the same numeric format.
Posted by Mark W. on July 11, 2001 8:37 AM
Perhaps, the fastest method is to use the
Data | Text to Columns... menu command, and that
presumes that an apostrophe (') prefix hasn't been
used. Are you familiar with this menu command?
Posted by junsport on July 11, 2001 8:43 AM
I am familiar witht the command but i dont see how it will convert the data into numeric format. All the data are numbers but the istext function noted some were true.
Posted by Mark W. on July 11, 2001 8:55 AM
When it parses the text representation of your dates
it will automatically recognize them as date values
and change their data types to numeric. It's magic!
Give it a try! : )
Posted by junsport on July 11, 2001 9:22 AM
Its still not working but thanks anyway.
Posted by junsport on July 11, 2001 9:31 AM
Posted by Mark W. on July 11, 2001 10:24 AM
Does it work if you manually edit one of these
dates? Have you thrown in the towel?
Posted by junsport on July 11, 2001 11:32 AM
Yes it works if i type them in manually. I guess i have to type it in.
Posted by Mark W. on July 11, 2001 11:51 AM
No. To me that means the Text to Columns... approach
should have worked. Did you not select the column
containing the text representations of date values,
and then press the [ Finish ] button at Step 1 of 3
on the Text to Column Wizard? Can you provide me
an example of one of these dates?
Posted by Sam on July 11, 2001 8:42 PM
Mark,
I have the same problem, but I'm trying to create a conditional sum formula and it won't recognize the values because they are text(exported from Access I think).
I'm gonna try your approach.
Posted by Mark W. on July 12, 2001 5:58 AM
Sam, you can also coerce a text representation of
an numeric value by adding 0 to it. For example,
="1"+0 produces 1
="1/1/2001"+0 produces 36892 (the datevalue for Jan 1)
