Created attachment 84049 [details] Sample spreadsheet with date column to be imported using the process above. Problem description: LibreOffice Calc does not sort date fields properly regardless of the format of the column if the spreadsheet is imported using the default (Standard) column formats. This behavoir does not occur in v3.5.7.2 on Ubuntu or v3.4.1 on Windows XP. It only appears to occur on Windows 7 / v4.1.0.4. Steps to reproduce: 1. Double-click to open the attached spreadsheet (1 column of dates) 2. Ensure that the following is set: - Character Set is Western 1280 or Unicode (UTF-8) - Separator = Comma - Quoted field as text = OFF - Detect special numbers = ON - DO NOT change any column types 3. Click on the column heading, select Sort. Ensure the following: - Sort by Field Name 'Date', ascending - Under Options, 'Range contains column labels' = ON 'Include formats' = ON (doesn't seem to matter) 'Enable natural sort' = OFF (doesn't seem to matter) 4. Click OK On a Windows 7 machine with 4.1.0.4, it will sort as text EX: 01/01/2000 01/01/2010 02/01/2000 02/01/2010 ... Ubuntu / Windows XP with the 3.x base, it will sort properly. To get it to work in Windows 7, you MUST select DATE(M/D/Y) as a column format when doing the initial import (step 2). Follow all the other steps and it will sort as dates. Current behavior: Sorts values as text or numeric, but not date. It appears to put a ' in front of the date field in Windows 7 only. Expected behavior: Sort dates as date and not text. Operating System: Windows 7 Version: 4.1.0.4 release Last worked in: 3.4 all versions
I forgot to mention that if you attempt steps 1-4 and then try to format the column, it still does not sort. Only when the formatting is done on the initial import screen does it work.
Seems that in your Windows environment the dates are imported as text and not interpreted as dates because the environment is not set to English (USA). Date recognition depends on the locale, or setting the column to an explicit Date(M/D/Y) type as you mentioned. Ensure that in the Text Import dialog the locale is set to one that matches the expected data, in this case for example English (USA).
Language recognition was the first thing I looked in to. The Windows 7 machine is using 'Default - English (USA)' as the language. LibreOffice did seem to want to default to 'System' as the character set on the import screen. I changed this to Western Europe (Windows-1252/WinLatin1) to match the settings on the Windows XP machine. I see your point, but that does not negate the bug request. This operation works correctly on other Windows / Linux platforms and also works correctly with MS Office 2007 on the same Windows 7 machine. The quirk is in the date recognition abilities of LibreOffice.
I played around with dates in CSV and for me "standard" never detected the date correctly as a date (only as text) no matter what locale or date format I chose in setting or system. It only detected it correctly if I manually changed the column from standard to the appropriate date format. I tried 4.0 or master on Linux and Windows 7.
*** Bug 68265 has been marked as a duplicate of this bug. ***
I am able to reproduce the date import problem in version 4.1.1, as per comment 4. It works correctly Windows 3.6.1 and 4.0.1
I can confirm this bug in LibreOffice 4.1.1.2 Build ID: 410m0(Build:2) on Linux Mint 13 64bit running the Gnome Shell. I've tried importing with different encodings (UTF-8, Western Europe (ISO-8859-2) and Western Europe (ASCII/US). I've also tried using different locales (though only English-US and English-UK, the latter being my default one), and 'detecting special characters', and still dates are not imported correctly no matter how you format the data (or without formatting it at all) Very annoying as it forces me to reboot into Windows and use Excel just to get this done properly
This is now working for me in Windows on 4.1.2, should it be closed now?
Works as expected if you choose the right column type in csv import dialog. Why do you want that Calc thinks for you? Closing as WorksForMe. Best regards. JBF
(In reply to comment #9) > Works as expected if you choose the right column type in csv import dialog. > Why do you want that Calc thinks for you? Because there are things like locale settings which know the date format I am using and should also use this date format by default at import. I should not need to set the column type unless the date is in another date format than expected.
I just re-tested this using 4.2.5 (on Windows), and the date values get imported as dates, and the sort works as expected. I didn't even have to change the column date type in the CSV dialog, but I did check the "Detect special numbers" option. I'm in US English locale, same as the initial reporter.