Ask Your Question

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

asked 2018-04-17 00:31:44 +0100

Liberty Belle gravatar image

updated 2018-04-17 02:19:26 +0100

erAck gravatar image

Version: 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:

image description

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2018-04-19 20:58:15.769264


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

Liberty Belle gravatar imageLiberty Belle ( 2018-04-17 00:47:21 +0100 )edit

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?).

Liberty Belle gravatar imageLiberty Belle ( 2018-04-17 00:54:43 +0100 )edit

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.

Liberty Belle gravatar imageLiberty Belle ( 2018-04-17 01:02:37 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-04-17 02:18:47 +0100

erAck gravatar image

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.

edit flag offensive delete link more


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.

Liberty Belle gravatar imageLiberty Belle ( 2018-04-17 03:31:43 +0100 )edit

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.)

Liberty Belle gravatar imageLiberty Belle ( 2018-04-17 03:38:34 +0100 )edit

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.

Liberty Belle gravatar imageLiberty Belle ( 2018-04-17 03:39:45 +0100 )edit

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.

erAck gravatar imageerAck ( 2018-04-18 12:13:15 +0100 )edit

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

Liberty Belle gravatar imageLiberty Belle ( 2018-04-19 18:38:00 +0100 )edit

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

paul1333g gravatar imagepaul1333g ( 2018-11-13 16:00:29 +0100 )edit

Question Tools

1 follower


Asked: 2018-04-17 00:31:44 +0100

Seen: 351 times

Last updated: Apr 17 '18