Ask Your Question
0

Sorting a table in Writer with text in the form 2/12 (meaning feb 2012)?

asked 2016-03-29 14:09:17 +0100

terry10k gravatar image

I have a large table in Writer originally prepared in MS Word (which I no longer have). I have a column in the format 2/12, 4/16, 12/09 etc (meaning Feb 2012, April 2016, Dec 2009 etc). In word I could sort this column in date format and it would get it correct ( ie sort the respective rows into year then month order - 12/09, 2/12, 4/16). It will only sort on the first numeric value (ie month). I've tried it in Writer and also copied to Calc and MS Excel - they interpret 4/12 as 4/12/16 whatever format I set date in. It is a large table which I need to sort in various ways; the other sorts are no problem as they are text.

Any help very much appreciated.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-03-30 14:25:53 +0100

paul1149 gravatar image

I was going to suggest reformatting the date cells to make sure LO was reading them correctly, but then discovered this bug: 98972:

edit flag offensive delete link more
0

answered 2016-03-29 23:55:09 +0100

Lupp gravatar image

updated 2016-04-01 22:33:09 +0100

Supposing your locale is accepting the D/M/Y pattern for dates and one of your dates is in A1, you may enter in a cell of a helper column one of the formulas =VALUE("1/"&A1) or =TEXT(VALUE("1/"&A1),"YYYY/MM"), the first one returning an actual date characterizing the month (first day of), and the second one returning a text describing the month in a passably reasonable format. In both cases the results can be used for sorting.

(Prefer formats according to ISO 8601. Do not use 2-digit-years; there are still dates from the 20th century in the world. Generally don't use "smart" formats. They may turn out to be silly.)

Edit1:
From the comment by @terry10k I have to conclude that he not actually used the formula I had suggested. With "2/12" e.g. in cell A1, the expression "1/"&A1 on the parameter position would evaluate to "1/2/12" and this accepted under the pattern D/M/Y by the VALUE function would result in 2012-02-01 (formatted in compliance with ISO 8601). For no locale I can think of it would evaluate to a date of the current year.
The month designators given in the OP were texts as terry10k stated. If being numeric (as paul1149 seemingly assumed) and formatted using the number format code "M/YY" they would have sorted correctly anyway.
For texts built from digits to sort correctly, it is indispensable that they comply with four conditions:
(1) They must be accepted under the same strict syntax.
(2) More significant parts must be left of less significant parts.
(3) Delimiters in the respective position must be identical.
(4) The number of digits used for a semantic part must always be the same.
Thus month designators in text form complying with the format code YYYY/MM will sort correctly. Built along the code M/YY they will sort wrongly.
Our remaining problem now is to convert badly formatted designators to wellformed ones. Unfortunately this is not quite simple, and the most simple ways will depend on the locale and/or certain user settings. For more details see the attached example.
(Isn't it ridiculous how we have to struggle because someone was too lazy to enter a few characters sensibly?) lof67456SortingMonthDesignators001.ods

edit flag offensive delete link more

Comments

Hello Paul. Thanks for the reply which I have tried; In calc the relevant column changes straight to the false date (current year) so the formulae can't work (I can see it would if I could paste as text but how?). I have also tried it in Writer and not got anywhere but that may be because I'm not sure of how to use formulas in Writer. Any further help will be much appreciated.

terry10k gravatar imageterry10k ( 2016-03-30 16:05:31 +0100 )edit

My apologies, that last comment should be addresses to Lup - sorry Lup, hope to hear from you soon!

terry10k gravatar imageterry10k ( 2016-03-30 18:07:00 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-03-29 14:09:17 +0100

Seen: 76 times

Last updated: Apr 01 '16