I have imported a table column with the following numbers from a text editor
06 2010
01 2012
01 2011
I use: Menu → Data → ”Text to Columns” → set the Column Type to “Date (DMY)"
Then I format the cells to User-defined MM YYYY (I want to retain the format)
Then I try to Sort Ascending – and got this result:
01 2011
01 2012
06 2010
As you know it should have been:
06 2010
01 2011
01 2012
Where did I go wrong / what did I misunderstand?
The same link as presented in one of your previous questions regarding time OASIS Open Document Format for Office Applications (OpenDocument) Version 1.2 - Part 2: Recalculated Formula (OpenFormula) Format explains what dates are in Calc (ODF). Thus sorting dates in calc is in fact the same thing as sorting numbers (dates are stored as an integer, where the number represent the days since 1899-12-30, which is day 0). You may check, whether Calc recognizes your data as dates, if you temporarily format as numbers and check whether they show up as an integer (TODAY() is 43764, the 43764th day after 1899-12-30; Tomorrow this info will be deprecated;-)).If the data don’t sort correctly your “dates” are (besides talking about bugs) in fact no dates according to the ODF format specification.
Where did I go wrong
… when assuming you (or better: LibreOffice) magically can recognize and convert something like 06 2010 into a correct date by just telling column type “Date (DMY)"
I wrote:
Date input requires a full date. Partial date input is not handled by Calc.
That is not quite right. However, you need to add something by way of separators or unambiguous data (month names) to have the input conforming to a date format Calc will take to be a date (use a “date acceptane pattern”). See comments from erAck and Opaque for details.
Cell formatting only affects how the content is displayed, not how input is interpreted. Also, even though you instructed Calc to interpret as dates, it can’t read the dates and falls back to making text cells.
Using the DATEVALUE() function may help, but I suspect that it doesn’t add much to Calc’s date recognition. You will get an error for content not handled as a date, which may be useful.
Probably better: Use space as separation character in Text to columns, and keep default column type. This will give two columns of numbers, one for month and one for year. You can sort on those columns as they are (first sort by year then by month). If you want a proper date, use the DATE() function to assemble year + month + 1 to a proper date value (which can also be sorted as you need).
Partial date input is not handled by Calc.
Yes it is. But it depends on locale and date acceptance patterns. If the date acceptance patterns include M Y even a space can be used as separator (though unusual and possibly confusing) in input. An input of 06 2019 then would yield a date 2019-06-01 (formatted as whatever the current locale prefers).
So why would this 06 2019 input not produce a date in my case?
Because your locale’s date acceptance pattern doesn’t include M Y (which is definitely not a standard date acceptance pattern as @erAck already stated - see Tools -> Options -> LibreOffice Calc -> Language Settings - > Languages -> Category: Language Of -> Option: Date acceptance pattern containing the date acceptance patterns). My opinion: Avoid this, because using this pattern would add information, which is not in your input (In given case it adds the information 1st day of the month).
Well it’s ok to add 1 to the “day-place-holder” and then It sounds like a less bulky way to do it - than the way which contains spitting and using the DATE() function