Imported csv date formatting adds an apostrophe to the beginning of dates

English
, , , ,
#1

Version: 6.0.3.2
Mac OS High Sierra 10.13.4

When I import a csv file and format the date column, LO adds an apostrophe to the beginning of each date. This apostrophe is only visible in the formula field. It’s really weird. How can I stop this behavior?

It actually prevents the date from formatting correctly. I had formatted it to MM/DD/YY and at first I couldn’t figure out why the formatting didn’t ‘take’. When I took a closer look, I saw the little apostrophe up in the formatting bar.

It looks like this is an old problem, as I found an old, unanswered thread on this: Why does format column to date insert apostrophe? '08/03/2015

image description

#2

Also, is there a way to ‘mass edit’ this? I’m having to go through each date and manually delete the apostrophe. Frustrating.

#3

As a point of clarity, LO is definitely not adding the apostrophe in place of a zero. Even for dates such as 11/11/2014, they still appear as '11/11/2014 in the reference field (formula field?).

#4

FYI, when I go in manually and remove each apostrophe, the date magically transforms to the correct formatting I had set up. '8/12/2015 changes to 08/12/15, and '11/11/2014 changes to 11/11/14 as soon as the $#! apostrophe is gone.

#5

The cell content is of type Text but could be interpreted as a date if re-entered. When importing the CSV, in the import dialog do not force the field to Text, and/or set the column type explicitly to Date.

See also this FAQ.

#6

Thank you for your response and the link.
To be clear, the apostrophe doesn’t appear upon importing the CSV file, it only appears once I format those cells to the specific MMDDYY date format. It may happen when changing to other date formats as well, I don’t know.
I tried opening this file again setting the column type to Date in the import dialog, but I still ran into the same problem once I formatted the cells to MMDDYY.

#7

Based on the FAQ you linked to, I tried the Clear Direct Formatting command, but this created some very screwy results.
Some of the dates turned into a series of five numbers (11/07/16 became 42681, for example).
Other dates remained as dates, but now appeared with the apostrophe in front (which previously did not appear unless I tried to more specifically format the date.)

#8

I then followed the Find and Replace instructions contained in the FAQ, which did remove the ’ from the dates, but left the dates-transformed-into-numbers as-is. So my original 11/07/16 is now 42681, and this happened with about half the dates on my spreadsheet.

#9

The number is the date serial number (days since null date). Just apply a Date format to the selected cells. Apparently you had mixed content, some of type Text and some of type Date, otherwise the Date types wouldn’t had changed to Number when clearing the formatting.

#10

OK, that worked. I hadn’t realized I would have to re-apply the Date format.

#11

with big column, I just selected copied paste in to notepad. copy and paste back in and all now works.

#12

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.