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?
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.
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.
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.
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.
Option 2. Import dialog when opening CSV file with Calc. Check Detect special numbersoption
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.
As @anon73440385 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.
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…
This doesn’t seem to be the right solution.
I encountered the same issue, and, had I not had some expertise, would have simply experienced this an inexplicable and quite unacceptable error in comparison to Excel.
The problem arises when importing CSV files , and would be avoided if ‘Detect special numbers’ was selected by default, and perhaps warnings in situations where that might produce misleading results (I assume this is the case, or why have the option)
I propose therefore that ‘Detect special numbers’ should be selected by default