Ask Your Question

Inserted numbers (dates) from a text editor, the dates are sorted incorrectly

asked 2019-10-26 12:41:32 +0200

T-buch gravatar image

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?

edit retag flag offensive close merge delete


The same link as presented in one of your previous questions regarding time 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.

Opaque gravatar imageOpaque ( 2019-10-26 13:08:33 +0200 )edit

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)"

Opaque gravatar imageOpaque ( 2019-10-26 13:54:01 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-10-26 12:54:01 +0200

keme gravatar image

updated 2019-10-27 09:39:18 +0200

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).

edit flag offensive delete link more



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).

erAck gravatar imageerAck ( 2019-10-26 16:24:37 +0200 )edit

So why would this 06 2019 input not produce a date in my case?

T-buch gravatar imageT-buch ( 2019-10-26 19:22:56 +0200 )edit

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).

Opaque gravatar imageOpaque ( 2019-10-26 20:50:28 +0200 )edit

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

T-buch gravatar imageT-buch ( 2019-10-27 06:55:29 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-10-26 12:41:32 +0200

Seen: 64 times

Last updated: Oct 27 '19