[Solved] Problem with Sort in Calc

[daffyd]

Hi Guys,

I have a strange problem, In 2003 I started a spreadsheet (.sxc back then), with a list of customer names, addresses and dates of next visit, which I add to and edit weekly.

My problem is, when I "select all" and "sort Decending" "by Column K (date column)", the results start at 2007-2009 and then 2003-2007 then June 2009 on.
I have tried "select colunm K" "format calls as Date" and sort again, which makes no difference. the problem seems to be with the various versions of Calc over the years, as the results seem to coincide with major upgrades.
I have noticed that Data entered with the first version (2003 .sxc) seems to have some sort of different formatting to the later version which came in 2007 (.ods), and new enteries made after the current version (3.0.1) was installed (in june 2009) seem different still.

any ideas welcome

Thanks in advance

Donald

[acknak]

The cell format has no effect on sorting. The sort function uses the cell's internal value, not the displayed value. A cell format can't change the internal value, only how the value is displayed.

My guess is that you have some dates stored as text, but I can only guess without seeing the actual sheet. Can you give us a look at it? Maybe that wouldn't be such a good idea if it has names and addresses in it.

Can you copy/paste the date column to a new sheet and attach that here? You can use the "Upload Attachment" link (below the message entry area after you click "POST REPLY"). [Forum] How to attach a document here

[daffyd]

Thanks for helping

hope the attached file helps, it is a sample of the three era's of my sheet.

Thanks again.

Donald.

[Villeroy]

Yet another spreadsheet formatted to death.
menu:View>Highlight Values shows the problem.
Select columns J, K and apply the desired date format or anything which is not "@" (text).
Remove number format "@" from column I. Set it to "General".
menu:Find&Replace...
[More Options]
[X] Current Selection
[X] Regular Expressions
Search: .+ (dot and plus)
Replace: &
{Replace All]

[acknak]

Maybe this will help explain. I've switched to "Value Highlighting" to show the values in different colors, then followed Villeroy's suggestion for converting to numeric values.

For more on how Calc handles dates, see: http://wiki.services.openoffice.org/wik ... e_overview

[daffyd]

Thank you Guys,

I use this as a simple list, don't know how all this formatting happened.

Regards Donald

[Villeroy]

Rule of thumb: Never use number format "@" unless you want numbers to be treated as text (sequence of digits without numeric value). Typically these "text numbers" are identifiers, phone numbers, zip codes you never calculate with. Nobody calculates a sum, difference or average of phone numbers.
Numeric text sorts alphabitically, which makes perfect sense when you sort zip codes or phone numbers of different lenght:
1
11
121
2
21
212
3
31
312

The equally looking numbers sort like this:
1
2
3
11
21
31
121
212
312
which is just perfect when you actually want to sort numbers.
The different values "123" vs 123 have nothing to do with formatting. No formatting attribute converts your numbers to text nor text to number. Number format "@" is just a switch to prepare cells for input of literal text.
Since nothing is supposed to modify your data by formatting attributes, it is required to re-enter or re-import your data. Replacing regular expression ".+" (any chars) with "&" (what has been matched) effectively re-enters all values for you.

menu:View>Highlight Values shows all numbers in blue.
Normally, you would detect numeric text imediately by its left bound orientation in the cell whereas numbers are right bound. Quite often people center everything to dissolve this disturbing appearance. This happened in the above attachment as well.

[daffyd]

Thanks for the help, this is the first spreadsheet that I created and it is very basic.
my knowledge of calc is zero and I really appreciate your assistance.

Regards

Donald

[Villeroy]

What I tried to explain (again, hopefully for more than 2 or 3 readers) is the same most fundamental basic knowlege for any software dealing with numbers. It is not advanced, not tricky, no secret knowlege. It is the first thing you have to understand when you try to do anything with some software that processes numbers. It has nothing to do with Calc, not even with spreadsheets since the difference between data types text and numbers is the same in thousands of applications.