Sorting spreadsheet rows by date column and not including headers keeps breaking

I use a spreadsheet to keep my check register. LibreOffice is supposed to be able to sort the data by rows in ascending order without including the headers (i.e. that says DATE). It will work for awhile, and then without me changing anything, it will break and every time I click in the date column and hit sort ascending, the header row ends up at the bottom of the data set. What is going wrong? The only thing I’ve found I can do is copy and paste all the data into a new tab and then it works again for a bit. How can I ensure the header column is marked as header to be excluded from sorting?

IF your header row contains subsequent strings (no blanks, no numbers) AND your data below the header contains true date values (no text like “1/2/22”), the sort buttons detect your header row and sort properly.
Hit Ctrl+F8. Do the dates appear in blue font colour? If not you imported wrong data from some text file or web page.
See
t82777.ods (18.4 KB)

Thanks for your response. I don’t import anything, I enter the date values myself, and the cell format is set as Date. I had it set to appear as you said (1/2/22), so I changed to the format in the doc (22-1-2) and no change- it still sorts and puts the headers at the bottom. It literally just started doing this again, the last time I opened and sorted, it worked fine. Nothing changed since then except I added entries at the bottom. I’m on a Mac, ctrl+F8 doesn’t do anything. I’ll try uploading screenshots.

Nevertheless, your dates seem to be text values. By default, text aligns to the left cell border as in your screenshot. However, it is impossible to tell from a screenshot what exactly could be wrong with your document. You might have noticed that the dates in my sample document sort correctly without the header row.

  1. Test: =COUNT(C4:C180) counts numeric values. If that test yields zero, all your dates are no dates.
  2. SImply add a blank row between the header and the data (like your 3rd row). You may hide it completely.

Unfortunately there are ways to inadvertently play tricks. And even we two “experienced users” will not know them all because we rarely apply formats tending to hide functionality (left adjusted dates, lots of borders in place of cell-grid lines e.g.), merge cells, have data-field headers somewere else but in the first row, …
If you select complete columns e.g. where field headers are in row 10, and then click the shortcut icon for a sort, the labels are not regarded.
To be clear: This is not the cause of the issue in the given case. Otherwise the output would start at first row, but… Who knows what versions may be buggy with respect to the playboard of icons experienced user rarely look at?
More concrete: The described issue may show a bug of a specific version, or may be caused by user-profile corruption.
Ceterum censeo: We should never format dates to two-digit-year. The tradition is an original sin.

@Villeroy:

This looks as if a change of the NumberFormat caused the intended change in appearance ===> actual numbers.
Many (most?) users tend to explicitly adjust cell display to what they find pretty (in the shown case also vertically), and next to always useful functionality requiring omisssion of stubborn formating is ignored - or unknown.
Everything teached the wrong way by???

However, the detection of header rows works better in OpenOffice. If there is a consecutive first row of text values, then AOO assumes one header row.

You won’t agree, but…
I’m not fond of automatisms supposed to break or be changed without “noted notice” at any time.
If I need a specific sort again and again, I write a few lines of user code for it. (Yes. You are right, the SortDescriptor is a kind of nightmare.)

:grin: That was tdf#91305, and fixing that was considered an improvement in just that - detection of the header row :wink:

table3.cxx (revision 19533948) - OpenGrok cross reference for /core/sc/source/core/data/table3.cxx (sorting rows)

  • Only one row in selection → no header;
  • Only one column → header is when first row has a text, and second has non-text;
  • First row has non-text → no header;
  • Second row has non-text → header;
  • Otherwise → no header.

It looks like the “only one column” check got redundant over the time (the following checks do the same) … but generally, how to improve it further?

Note that there’s also tdf#148679, which would allow to define a database range, and use its property for the sorting’s header detection.

See also: How do I sort rows without including first row?

Do the quick sort buttons detect the db range meanwhile?

I did that COUNT test and it yielded 176 - so my dates are dates then based on what you said, yes?

And adding the blank row worked!! For the moment at least! Thank you.

Unfortunately, not (yet), as the bug report tells. That is the task - to make them detect it.