Ask Your Question

Date Format adding quote mark

asked 2017-10-20 11:24:37 +0200

Umair gravatar image

I have downloaded transactions from the bank . I wanted to filter it by date. every time I change the format of the cell to date, it added a " ' " mark in the beginning of the text.

never had this issue Microsoft excel. please see the attached sheet.

edit retag flag offensive close merge delete


The apostrophe (single quote) shouldn't be actual content of the cells, but only prefixed to the display in the formula bar (and to the cell's content if you edit in the cell's area) to give a hint that something looking like numeric data (as dates are treated by spreadsheets) in fact is text content of the cell. True spreadsheet dates are integer numbers internally, and only displayed in a convenient format.

Lupp gravatar imageLupp ( 2017-10-20 12:00:25 +0200 )edit

If the apostrophe is also shown in unselected cells, my assumption based on experience is wrong. Please edit your question to get this clear. If the uncommon case applies, you will get a modifyed answer to help you.

Lupp gravatar imageLupp ( 2017-10-20 12:07:30 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-10-20 11:51:59 +0200

Lupp gravatar image

Quoting @Umair: "I have downloaded ..."
You surely got a table contained in a plain text file regarding the CSV conventions basically. When "opening" such a file with LibO Calc you actually import and convert the contents. And you are prompted to choose the setting for the import/conversion process because there are many versions of "CSV" and in specific the suppliers of those CSV generally don't regard a reasonable standard for dates. In your downloaded file a "date" will be a text looking like "7/12/17" e.g. which is ambiguous in a hazardous way. Calc must disambiguate this to get a date represented in the numeric way generally used by spreadsheets.

In the mentioned dialogue poping up:
-1- Enable 'Detect special numbers'
-2- Select the column containing your dates by clicking on the header label.
-3- Open the list next to 'Column type:' and select the order of year, month, and date as contained in the actual data.
-4- 'OK'.

For a column of dates already imported as text you get offered the needed tool via 'Data' > 'Text to Columns...'. data.

Don't forget to tell you bank they shall use the internationally approved and unambiguous date format YYYY-MM-DD (ISO8601) in the future to avoid such problems for their customers. Bank accounting data isn't a letter to aunt Alice, after all!

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-10-20 11:24:37 +0200

Seen: 884 times

Last updated: Oct 20 '17