Description: I have defined a column as date column. When i try to sort this out by ascending or descending,the dates don't sort , but the behavior is rather sorted as a numerical string. This should not occur and with little change in algorithm or forced behavior, dates entered in the various date format should sort by Month Jan - Dec ( 1-12) followed by Sort order of dates ( 1-31) , followed by sort order of Year ( in YY or YYYY ) Steps to Reproduce: 1. Create columns with various dates representing every month , few dates, few years such as in attached list 2. Select sort area 3. Select sort by ascending and descending. Actual Results: I ordered the dates by ascending. This is how it is sorted 1/13/17 1/26/17 1/26/17 1/26/17 1/27/17 1/28/17 1/3/17 1/3/17 1/5/17 1/9/17 10/18/16 10/19/16 10/20/16 10/24/16 10/25/16 10/27/16 11/17/17 11/19/16 11/22/16 11/23/16 11/25/16 11/25/16 11/9/16 12/10/16 12/13/16 12/14/16 12/18/17 12/19/16 12/2/16 12/20/16 12/20/16 12/21/16 12/23/16 12/24/16 12/26/16 12/26/16 12/27/16 12/28/16 12/28/16 12/29/16 12/3/16 12/3/16 2/15/17 2/7/17 3/3/17 3/30/17 3/30/17 3/31/17 3/31/17 4/28/17 4/3/17 5/5/17 8/12/16 8/12/16 8/16/16 8/17/16 8/18/16 8/20/16 8/22/16 8/23/16 8/23/16 8/23/16 8/24/16 8/24/16 8/24/16 8/24/16 8/25/16 8/25/16 8/26/16 8/26/16 8/26/16 8/26/16 8/3/16 8/30/16 8/30/16 8/31/16 8/8/16 9/12/16 9/13/16 9/14/16 9/14/16 9/16/16 9/16/16 9/16/16 9/19/16 9/25/17 9/26/16 9/26/16 9/27/17 9/28/16 9/29/16 9/3/16 9/6/16 9/7/16 9/9/16 Expected Results: 8/12/16 8/12/16 8/16/16 8/17/16 8/18/16 8/20/16 8/22/16 8/23/16 8/23/16 8/23/16 8/24/16 8/24/16 8/24/16 8/24/16 8/25/16 8/25/16 8/26/16 8/26/16 8/26/16 8/26/16 8/3/16 8/30/16 8/30/16 8/31/16 8/8/16 9/12/16 9/13/16 9/14/16 9/14/16 9/16/16 9/16/16 9/16/16 9/19/16 9/25/17 9/26/16 9/26/16 9/27/17 9/28/16 9/29/16 9/3/16 9/6/16 9/7/16 9/9/16 10/18/16 10/19/16 10/20/16 10/24/16 10/25/16 10/27/16 11/17/17 11/19/16 11/22/16 11/23/16 11/25/16 11/25/16 11/9/16 12/10/16 12/13/16 12/14/16 12/18/17 12/19/16 12/2/16 12/20/16 12/20/16 12/21/16 12/23/16 12/24/16 12/26/16 12/26/16 12/27/16 12/28/16 12/28/16 12/29/16 12/3/16 12/3/16 1/13/17 1/26/17 1/26/17 1/26/17 1/27/17 1/28/17 1/3/17 1/3/17 1/5/17 1/9/17 2/15/17 2/7/17 3/3/17 3/30/17 3/30/17 3/31/17 3/31/17 4/28/17 4/3/17 5/5/17 Reproducible: Always User Profile Reset: No Additional Info: Sort algorithm must order by by year , then sort by month ascending from 01 to 12 or Jan - Dec then Sort by Date User-Agent: Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36
Please attach the original file. I suspect, that you do not have dates but text, that look like dates.
Created attachment 141220 [details] Screen shot of Cell Date based data type
Created attachment 141221 [details] Actual CSV file stored as XLSX format Actual excel spread
Uploaded both screen shot of cell data type and also actual spread sheet. Please try sorting by column labeled appointment.
(In reply to NARAYAN from comment #4) > Uploaded both screen shot of cell data type and also actual spread sheet. > Please try sorting by column labeled appointment. Looks like every "date" entered into the "appointment" column has been escaped to text with a leading ' --apostrophe. Clear that and the dates will follow the input pattern and will sort by date. => NAB
The character U+0027 : APOSTROPHE {APL quote} Upon Normal Copy & Paste into text pad, this is not visible or available. However also upon CTRL + H find and replace '. It says No character found. However this character is visible if I open the cell individually only inside the cell
Created attachment 141223 [details] New file without the unicode apostrophe character See attached file. Problem of sorting still persists. Meanwhile , perhaps another issue, why the unicode character was not recognized in Find & Replace function.
Interestingly, I did another test. Just copied the column alone and sorted. It sorts fine.
Created attachment 141224 [details] Second file showing same sort behaviour Another file, showing same sorting issue
The "date" is indeed a text. You can verify that quickly with the toggle shortcut key Ctrl+F8. True numbers and dates are shown in blue. The file "ActualCSV file stored as XLSX format" has already the dates as text. Excel considers the file as broken and "repairs" it. To repair the document in LibreOffice do these steps: 1. Open document 2. Create a new column after column C 3. Copy the entire column C to clipboard 4. Click on newly cell D1 5. Paste the clipboard as "Unformated text". That is one of the options, when you click on the little triangle at the "Paste" icon. You get a csv-import dialog. 6. In section "Field" of this dialog click on the column header "Standard". That activates the drop-down list "Column type". 7. From that list use the item "Date (MDY)". 8. Compare the columns C and D to make sure the content is correctly converted. 9. Delete column C. 10. Do the same with column D "sent_at" and column F "received_at". Your current workflow which generates this file has these shortcomings: A. There is no need to wrap CSV data into an Excel fragment. LibreOffice (and data bases in general) can work with CSV data directly. B. Never use date in a CSV file in local notation. But always store dates in ISO format. It will then be read correctly by all application, that use this CSV file.