Ubuntu: Problem with sorting date in LibreOffice Calc



Question:

I'm very lost in this situation:

My objective is to sort a series of dates. When I try to sort dates in a column [for example 12/06, 09/16, 08/13 (MM/YY)], what I got is like this:

  • 08/13
  • 09/16
  • 12/06

The problem is that December 2006 comes before August 2013!


Solution:1

The problem is that the column you are trying to sort does not contain values of data type date, but most probably text strings. In order to sort them you will have to convert them into date. E.g. if the data is in field A2, you could enter in B2

=DATE(RIGHT(A2;2);LEFT(A2;2);28)  

Note that date format also needs a day (I choose 28 for simplicity).

Also note that just changing the formatting (the visual representation of the data) will not change its data type (and that is a good thing).

The datum contained in any cell will have one of five types: a text string type, a number type, a formula type, a boolean type or an error type. These five types of data values can then have various display formats so that, for instance, a number value can be displayed as a number, a monetary amount, a date or a time. Text strings are sequences of characters and punctuation marks and could, for example, contain textual information such as people's names. Number values are simply numbers but may be input and displayed in various formats including decimal numbers, dates, times, and numbers in scientific notation. (this is actually an extract from GNUMERIC documentation, but it is clearer written than anything I found for libreoffice).

You will have to use a formula to change the data type, as calc cannot make a sane prediction as to what you mean by 12/06.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »