LibreOffice 5.0 not sorting sensibly

3 replies [Last post]
amenex
Offline
Joined: 01/03/2015

My Raw Access files come to me from cPanel with their dates in the form DD/MMM/YYYY as
in 01/Apr/2018. I am now facing a spreadsheet of 5000+ rows and A through O columns which
I would like to sort in day-to-day order, Day 1 through Day 31, for example. What I am
getting instead is 01/Jan/2018, 01/Feb/2018. etc. which isn't a rational sort.

I've drawn a complete blank in my efforts to convert DD/MMM/YYYY to MM/DD/YYYY, except
by doing it longhand, which is tedious.

If either one of these puzzles can be solved, I will be happy, as I can indeed sort on
the date when it is in the format MM/DD/YYYY, which is all numbers.

Thanks,

George Langford

andyprough
Offline
Joined: 02/12/2015

Might want to check out this page, see if you have the same trouble: https://ask.libreoffice.org/en/question/6466/trouble-formatting-date-cells-in-calc/

If not, I can post a series of formulas that will do the job for you.

amenex
Offline
Joined: 01/03/2015

With no criticism of andyprough intended, the linked LibreOffice page is last dated 2013,
and the LibreOffice in my version of Trisquel_8 is LibreOffice_5, which has five years
of improvements in it. On top of that, the solution given depends on an unexplained
substitution of "Find: .*" and "Replace: &" for Find "'" and Replace "" which would
require much trial and error effort on my part to make it work.

Instead, in about two hours of harrowing copy & paste I changed all the misbehaving
DD/MMM/YYYY to MM/DD/YYYY, made all the more complex by the alphabetic sorting of
the months:
01/Apr/2018, 01/Feb/2018, 01/Jan/2018, 01/Jun/2018, 01/Mar/2018, 01/May/2018, wherein
it isn't easy for these old eyes to pick out the transitions from Jan to Jun or Mar
to May. [In my original posting I overlooked the goofy alphabetization of the months
which I've corrected above.]

This was made slightly easier by the ability to copy and fill with the Ctrl key held
down, which prevents incrementation of any numbers in the items copied; instead of
5000+ copies and pastes, I had about 1/30th as many to do.

Afterwards, sorting on the corrected dates took only a blink of an eye and put the
data in correct temporal order. The version of LibreOffice in Trisquel_8 is vastly
superior to the version in Trisquel_7 in this respect.

Thanks,
George Langford

andyprough
Offline
Joined: 02/12/2015

Excellent George, glad you were able to get it done. There are some Calc cell formulas that would automate the task a bit, but your way of doing it may be faster than learning to use a bunch of formulas.