Bug 116891 - Sorting of date by ascending and descending is incorrect
Summary: Sorting of date by ascending and descending is incorrect
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.0.2.1 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-04-08 23:06 UTC by NARAYAN
Modified: 2018-04-09 07:30 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Screen shot of Cell Date based data type (166.19 KB, image/png)
2018-04-09 00:46 UTC, NARAYAN
Details
Actual CSV file stored as XLSX format (8.60 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-04-09 00:48 UTC, NARAYAN
Details
New file without the unicode apostrophe character (8.45 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-04-09 03:30 UTC, NARAYAN
Details
Second file showing same sort behaviour (8.23 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-04-09 03:45 UTC, NARAYAN
Details

Note You need to log in before you can comment on or make changes to this bug.
Description NARAYAN 2018-04-08 23:06:58 UTC
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
Comment 1 Regina Henschel 2018-04-08 23:58:12 UTC
Please attach the original file. I suspect, that you do not have dates but text, that look like dates.
Comment 2 NARAYAN 2018-04-09 00:46:42 UTC
Created attachment 141220 [details]
Screen shot of Cell Date based data type
Comment 3 NARAYAN 2018-04-09 00:48:45 UTC
Created attachment 141221 [details]
Actual CSV file stored as XLSX format

Actual excel spread
Comment 4 NARAYAN 2018-04-09 00:50:01 UTC
Uploaded both screen shot of cell data type and also actual spread sheet. 
Please try sorting by column labeled appointment.
Comment 5 V Stuart Foote 2018-04-09 02:45:09 UTC
(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
Comment 6 NARAYAN 2018-04-09 03:22:55 UTC
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
Comment 7 NARAYAN 2018-04-09 03:30:41 UTC
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.
Comment 8 NARAYAN 2018-04-09 03:33:13 UTC
Interestingly, I did another test. 
Just copied the column alone and sorted. It sorts fine.
Comment 9 NARAYAN 2018-04-09 03:45:41 UTC
Created attachment 141224 [details]
Second file showing same sort behaviour

Another file, showing same sorting issue
Comment 10 Regina Henschel 2018-04-09 07:30:39 UTC
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.