I downloaded a csv file from my bank and the date value is preceded by the character ’
As a result the column will not sort.
Do you want to change the data inplace or create another column without the single tick character ?
Hello @Silver77, Can you edit your question and write a sample of how the date is shown, including the '? Once written, select it and press Ctrl+K (this will add ` marks). Thanks.
I think that the problem can be solved while opening the csv file.
Hello,
the ' is an indicator that LibreOffice recognized that the date (text) could be also a number (real date) and can be easily converted to a number using Data -> Text To Columns and just clicking OK (no settings in the dialog required).
Note: Dates in Calc are integer numbers counting the days since 1899-12-30 (which is day 0) - see OASIS ODF Specification - Section 4.3.3 Date and related setting Tools -> Options -> LibreOffice Calc -> Calculate -> Section: Date -> Option: [o] 12/30/1899 (default))
Hope that helps.
This doesn’t work for me, nor did any of any of the other permutations of the “format cells” menu. And yes, I am aware of how spreadsheets handle dates and times, but that information seems irrelevant to the question at hand.
Have you set Tools -> Autocorrect Options -> Tab: Localized Options -> Option : Single Ouotes [x] Replace? If yes - your single quote may have been replaced by a typographical quote of your default font during import and your dates are really text containing a typographical single quote.
( Accidental remark and neglecting that opinions are not welcome on this site: From my perspective a dumb default in a spreadsheet application)
hello @Silver77,
just try this tip:
There is a solution for this. It involves a “find and replace” function. From the “Edit” menu, choose “Find & Replace.” In the “Find” box, type “^.” (i.e., carat + period). In the “Replace” box, type “&” (without the quotes, of course). But you have to do one more thing for this to work. At the bottom of the “Find & Replace” window you’ll see a button that says “More Options.” Click on it, then select the box next to “Regular Expressions.” You should now have a “Find & Replace” box that looks like this: …
from this website: - click to enter -
P.S. ‘solved marks’ and ‘likes’ welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the “^” above it if you ‘like’ the answer,
“v” if you don’t,
do not! use ‘answer’ to add info to your question, either edit the question or add a comment,
‘answer’ only if you found a solution yourself …
This didn’t work for me.
@Rictus: just provide a sample, we’ll try to help, as it worked for others there is a good chance to get it to work for you, but nobody can ‘heal’ your bug on different data where it’s not apparent …
I have this problem with Bank data in New Zealand. The easy solution is to deal with it when you are downloading the file.
(1) Download the csv
(2) in the dialogue, open with LibreOffice Calc
(3) This opens the ‘Text Import’ box
(4) In this box, you will see a column that has the date in it, with “Standard” written on the top. Rightclick on this cell and choose one of the date options.
Whenever I have forgotten to deal with the date at this point, I have been stuck.
1 Like
You might also consider checking the box for Detect Special Numbers; you then shouldn’t need step 4.
1 Like