Calc Data/Sort failure

English
#1

Columns B C and D rows 1-16 below is the problem data area. Example, only 16 of 1017 rows pasted. (Note Col A is just a sequential line count, static, NOT part of the data array).
Number in Col B is the original Col A position before a previous sort of Col C categories. both A and B check data integrity (no accidental loss of data, traceability to original array).
391 11-Jul-22 Sale Col A counts lines (1 - 1017). Col B is the original Col A line count
383 11-Jul-22 Sale Data is in Cols C to M, but problem is evident with just these 3.
371 11-Jul-22 Sale
95 14-Nov-22 Sale
96 14-Nov-22 Sale My SORT criterion was (select Cols B C D rows 1-!6) sort Col C
57 15-Dec-22 Sale Ascending. (which SHOULD give data in DATE order).
56 15-Dec-22 Sale This is the result - as you can see, no effective sort.
55 15-Dec-22 Sale Help, please ?
54 15-Dec-22 Sale
53 15-Dec-22 Sale
52 15-Dec-22 Sale
47 19-Dec-22 Sale
162 20-Oct-22 Sale
160 20-Oct-22 Sale
302 25-Aug-22 Sale
300 25-Aug-22 Sale
#2

there are no dates in column C its just Text so it is sorted in alphabetic order, but not in numeric order.
Try to repair Column C with →Data→Text to Columns, similar to:

textual_dates
textual_dates1003Ă—735 88.4 KB

2 Likes
#3

Interim reply - Thank you. On my spreadsheet, Col C is not text, it’s totally date formatted already. I will try your solution. Perhaps a different date format will work. Can’t try right now, am called away … tomorrow perhaps.

#4

You confuse data type with format. You imported these dates wrongly. They ARE text regardless of formatting. Change the number format to anything and nothing will happen because number formats apply to numbers.

#5

Strictly speaking, this is not the case, even if we are talking about the displayed value of the cell.
Apply the @@ format to a cell containing a number and to a cell containing text. :slightly_smiling_face:

#6

Well, I think it is more helpful to forget about these rarely used “text formats” for now. The problem is (as always) that dates have been imported wrongly and no number format turns water into wine.

#7

Yes, most Calc users (as well as Excel) don’t realize that cell formatting can change the cell’s displayed value, but it doesn’t change the cell’s value (number or text for Calc).
For example, a typical false expectation is the concatenation of text with a cell that displays a date.

#8

t90208.ods (23.0 KB)
correctly imported data with numbers and dates.

#9

Press Ctrl+F8 (menu View - Value Highlighting). If they are dates, will be shown in blue; if they are text, in black.
Now press Ctrl+F8 to toggle to normal view.

1 Like
#10

Please see reply to LeroyG

#11

Villeroy, LeroyG … Villeroy’s datasheet includes dates as numbers (19/03/23)
What he doesn’t say is how he converted them
I tried entering same format in blank cell above my data, then the copy format/pasting (via brush) - no change.
I also tried reformatting the “date” column data via cell/format/date, that didnt work either. A few, I’d do manually. There’s 1017 dates - too many, there must be a way?,

#12

I copied the data from your posting and used paste-special as unformatted text. In the import dialog (which is the same as for csv import) I checked “detect special numbers” (always check this!) and selected “English (Australia)” as import locale. Any non-US English will do. You would choose “English (USA)” when importing dates like “Jan-1-2023” (Month-Day-Year date). You may also use “English (USA)” but then you have to mark the date column as DMY date.

You can copy the wrong text date cells and paste via menu:Edit>PasteSpecial>Paste unformatted (Ctrl+Alt+Shift+V) with the above options English (non-US) and “special numbers”

1 Like
#13

Thanks, Andreas. Original data was a .csv file, you’ve answered (solved) the question - thanks again.

#14

Also see this FAQ.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.