In Calc V7.2.3.2 I imported a date via CSV like 2022/2/25 and it gets put into a cell properly in text format. But when I try to change the format of that cell to Date, it shows that the cell is formatted as Date, but when it does the format, it puts a tic-mark ( ’ ) in front of the date and does not sort correctly. Why is the tic-mark put there and how can I get rid of it?
TIA!
The formatting doesn’t change data, only what is shown. Date as formating is only possible if the string is converted to an integer representing a date during import. How to do this, I’ve put in my answer below
The mark ’ shows your data is imported as Text, and NOT as a Date (to be interpreted as a number of days since the end of year 1899)
Best way to change this is to repeat the import and change the type of the column in an fitting date. The shown grid is not only a preview, but actually a control to change the types, if necessary.
Important: YOU must check/give the right type, as it us not always possible to guess this right and defaults may depend on your locale settings.
Quick’n dirty: To fix already imported data, mark it, select from menu Data “Text in columns” and select the appropiate type in the same grid as you see during import. (As usually: Try this with a copy of your file or as minimum precaurion - think before you save the modified file…)
Thank you. I thought changing the format would look at the data and change what it saw to a date. I did not understand how the import works, but now thanks to you and erAck I do and can get it to work.