Ask Your Question

sort by date problem in spreadsheet

asked 2019-07-09 18:46:03 +0100

andrew07 gravatar image

Data "Sort" of the date field does it by numeric sum of the 3 date fields rather than by date, even when I changed format for that column to "date". E.g. 2/14/2009 is considered 2015 to the sort algorithm. How can I cause the sort to recognize and sort by actual date?

edit retag flag offensive close merge delete


No, nothing is summed or any strange operation performed. LO sort does it by the numeric value of the date and if the values are not numeric they are not dates. Please provide an anonymized example where the sort runs wrong (for your file). Background information: LibreOffice stores dates as numeric value (data) and the format is just to change the way these data are presented to the user (data representation). So date is an integer counting the number of days since 30.12.1899 (default) and time values are a fraction of a day.

Opaque gravatar imageOpaque ( 2019-07-09 19:15:55 +0100 )edit

The way you have described the operation is how I had expected it to work (as it does in Excel). However, this is not what I am experiencing! The column is formated to be "date" and yet this isn't how it is handled. I would like to send the file to you -- perhaps you can detect the problem -- please advise how I can do this.

andrew07 gravatar imageandrew07 ( 2019-07-09 19:39:09 +0100 )edit

Upload here using the clip symbol in the edit function. Edit your question for that purpose. And please provide the version of LibreOffice and your operating system - since bugs could be always a reason for strange behaviour.

Opaque gravatar imageOpaque ( 2019-07-09 19:45:08 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-07-09 21:43:00 +0100

updated 2019-07-09 21:49:20 +0100

Hello @andrew07

Seems you have imported your CSV file without defining that column with dates should be treated as Datetime value, not text string. You have several options to do so:

Option 1. Import dialog when opening CSV file with Calc. Select column needed and select Column type Date with correct format.

Import CSV date column

Option 2. Import dialog when opening CSV file with Calc. Check Detect special numbersoption

CSV import Detect special numbers

Option 3. When CSV data is already imported, select column needed and go to menu item Data -> Text to Columns In the dialog window select correct Column type for date column - pretty the same as option 1.

Text to columns

As @Opaque already mentioned, text strings and numbers are sorted with completely different sorting rules applied, so while your dates are not of correct type, you will get unexpected results.

even when I changed format for that column to "date"

This will change only display options for the specific cell, how the cell data is displayed. Changing format will not affect data type of this cell. So if you select some date/time format for the cell, it tells Calc how date or time should be displayed in this cell, but does not define/convert data type of this specific cell. Text stays text.

edit flag offensive delete link more


See also this FAQ.

erAck gravatar imageerAck ( 2019-07-10 12:30:16 +0100 )edit

Your suggestion worked! Thank you! I had utilized "format cells" to change to date format and apparently this isn't sufficient. You are correct that I had imported from csv formated file. I really appreciate your help as this solution hadn't occurred to me nor was I able to determine the problem using the Help library...

andrew07 gravatar imageandrew07 ( 2019-07-10 14:28:55 +0100 )edit
Login/Signup to Answer

Question Tools



Asked: 2019-07-09 18:46:03 +0100

Seen: 215 times

Last updated: Jul 09 '19