Sorting on dates in LibreOffice Calc

ithepact · June 11, 2022, 1:10pm

I downloaded a csv file from my bank and saved it as an ‘xls’ file. The file contains dates in one of the columns as DD/MM/YYYY. e.g 01/09/2022, 15/11/2021 etc.

Using ‘Format cells’ I have set the column type as a ‘DATE’ with a format of DD/MM/YYYY.

When I sort the list the dates are treated as numeric i.e all the 01s are together etc. ignoring the Months and Years. Any help will be greatly appreciated. Thanks Peter.

karolus · June 11, 2022, 1:22pm

Hallo
Meanwhile in the …csv-Import-Dialog you need to change in the preview the respective Columns from Standard to Date d m y

ithepact · June 12, 2022, 4:55am

Hi thanks for taking the time to respond to my query, I’m really appreciative.
I’m not really sure how to do what you suggest as in the bank website I can select the type of file to download (i.e csv) and a Download button.
I open the file in Calc and Save as an XLS file.

EarnestAl · June 12, 2022, 5:55am

You can download in CSV, no problem there.

Open Calc and select File > Open and navigate to your downloaded CSV file, select it and OK
.
A Text Import dialogue will appear,
- tick the appropriate box for the separator, e.g. comma. You should see the text separate into columns if the incorrect separator was initially selected
- Tick the box Special Numbers if currency symbols, e.g. $, CHF, etc. are in the csv
- Click the column header on the Date column then select Date (DMY) from the Column Type field. Note that you have to determine the format of the date in the CSV; from the image below you can see it is DMY because in the column there is 14/01/22 and there aren’t 14 months in a year.

NOTE: Always work and save in native format (.ods for LibreOffice Calc). Only export to xls if required to share with others

ithepact · June 12, 2022, 6:28am

Brilliant… nice and concise and fixed the problem.

erAck · June 11, 2022, 7:04pm

Your “dates” are not numeric dates but just text strings that happen to look like dates, hence are sorted as text and not dates. Applying a number format does not change the cell content ot its type. See this FAQ for how to convert.