LibreOffice 5.0 not sorting sensibly
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
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.
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
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.