Cannot sort by English (UK) format date

[ocorlett]

I installed LibreOffice in the US, so everything defaults to English (USA) formats. That includes the date, whereby M/D/Y is standard.
I have an imported spreadsheet where the date is in English format (D/M/Y). It is sorted with the date column as descending (latest first) and I want to sort the sheet so the dates are ascending (latest last).
I can’t get it to work. Here’s what I tried:

(1) Tools/Options/Language Settings/Languages - change everything to English(UK), including Date Acceptance Pattern as D/M/Y. Format the date column to DD/MM/YY (the English format). When I sort (ascending) on the date column, it sorts it as if it were text or a number or something. All the initial entries are 01/XX/XX, then it goes to 02/XX/XX and so on.
For example, 02/09/22 comes before 02/11/21 (In English format that would mean 2nd of September 2022 is earlier than 2nd of November 2021; even in American format it is incorrect).
(2) I tried making a new column, formatting it as Date DD/MM/YY, assigning by formula from the original date column, copy/paste special values and formats so that the new column entries are no longerby formula, then trying the same sort on the new column. The result is the same.

I realize I could make columns which extract the DD, MM, YY and concatenate them into US format before doing the sort, but as I’m going to be doing this quite frequently I would like to find an easier way.

Can anyone please tell me what I am doing wrong?

[ajlittoz]

Use an auxiliary column as you suspected. Copy the data from the date column =<date_cell> but format this column as Number. Sort according to this column.

What this does is copy the date-time information and removes any interpretation, just keeping the encoding which is a number. This number has an increasing value along time axis.

Check however that your “foreign” date data is really a number not a string otherwise it won’t work.

[ocorlett]

Hi, and thanks for the suggestion. I had to modify your method very slightly:

(1) Using Tools/Options/Language Settings/Languages, I changed everything to English(UK). (I checked “For this document only”, but it actually changed for all my spreadsheets, which was a bit annoying!)
(2) I formatted the original English date column A as Date, using Default English(UK), which switched the date format to DD/MM/YY.
(3) I formatted a new column I as Number
(4) When I put in a formula in the new column I, referencing the original column A entries, all it did was copy it exactly into the new column. eg in cell I2, I tried =A2
(5) So instead I tried using 1*. eg for cell I2 in the new column, I used: =1*A2. That did it!
(6) I copy-pasted the numbers as values, and the sort worked.

Thank you for your help!

[erAck]

Your original “dates” were not dates but text looking like dates. The =1*A2 applied some magic implicit conversion from date text to number, that may or may not work and also depends on the Tools → Options → Calc → Formula, Detailed Calculation Settings, Custom, Details, Contents to Numbers, Conversion from text to number, and the locale if set so (default because that’s the mess Excel does).

For proper conversion see also this FAQ.