Hi.
I have a largeish CSV I downloaded from bank, for my accountant. I need to be able to keep it sorted by date.
I have done significant hunting around the web to try and get this working. The common answer messes up my data really badly. In every variation I have been able to find.
I also see a lot of people saying this doesn’t work and no acceptable solutions in most of the threads!
I have tried the following.
Data> sort ascending or descending. Chose to extend at the pop up.
Result: Sorts by day only and ignores the month and year.
format>number>date
Sort as above, same result.
{Started googling}
data>text to data.
Set column as D/M/Y
Result: Immediately sorts column by day number only. Does not sort other columns at all. entire spreadsheet of data is ruined unless I ctrl+z
Attempting Data>sort makes no change. Months and years are ignored.
Import again with detect special characters.
this is nice because it changes from left to right justification from month to month. But its removed the half the year data on every right justified month. so 2021 becomes 21. But ts got the same sorting problems. Month and year ignored. No way to sort rows by date
Import and declare column as D/M/Y date format. with and without detect special characters.
Result. Imports the data with date column only arranged incorrectly. Dates are no longer correct for transactions. days only sorted. same as all above. This was by far the worst solution offered because it destroys all the data on import!!
Summery. After checking 20+ other help threads and confirming the above methods are the recommended way. Have found this does not work. every time it sorts by day only and always ignores the month and year. Furthermore. If you do it the import method. It will mess up your entire spreadsheet by moving dates only, and not moving the entire row the date is on. There is no way to undo this. You need to delete and start again. Hopefully you didn’t save!!!
Slightly off topic, is there a way to lock the rows? So if I do sort a column it will keep rows intact? For this spreadsheet I would not ever want to have any column sort without keeping rows intact.
I have attached 2 version of the file with all personal data removed. Its just a column of dates to sort. Added data in a few random cells as a form of checksum, to confirm rows are being sorted by date, and not just the dates being sorted.
one version is with special character detect, the other Raw version only had separate by comma on during import, which was required to get the columns to be detected.
example date sort detect special.ods
Update:
Found part of the problem.
If I click on a date cell. In the formula bar there is a ’ that does not show in the cell. Same as Problem with sorting dates
When importing I checked the other option and put ’ in the box. This imported the data correctly.
I couldn’t find a way to delete this character from all cells in the column. So a re-import is required. Hope you didn’t save over the original .csv… I did first time. Rookie mistake. Luckily I way able to re-download the raw text data.
However. If I try to declare the date column at import or after via text to data. It rearranges the format to m/d/y and sorts by month only ignoring the day and year. It also destroys the rows, then asks me if I’d like to extend the sort to other columns, but its too late. the spreadsheet is already corrupted due to rows not being maintained… It also changes 2021 to 21, which is not unworkable, but I don’t like that it just deletes my data without asking. I’d strongly prefer to keep year as 4 digits.
Possibly I have the date format set to M/D/Y in some other setting? This makes no sense as I always prefer D/M/Y as its the Australian standard format. Looking through my preferences while I wait for further help. will update next post.
Still no luck getting it to sort by whole date. Can only sort by whatever is before the first slash.