Correctly structured date text formats into a non-date by placing '

[Haas]

• A cell is formatted as text (Format code = @).
• The content of the cell is “25-12-20”.
• I have the following accepted date
  input patterns defined in language
  settings: “D-M-Y; D/M/Y”.
• Now when I format the cell as a date (any form thereof) it merely places a ’ in front of it, making: " '25-12-20 ".

It thereby breaks its own (previously) correct format and hence doesn’t recognize it as a date anymore.
When I manually remove the first apostrophe character it jumps to the desired date format…

This thing has been bothering me for months. I’ve tried a bunch of settings in Calc and Windows, but nothing works.
I have a gruesome manual workaround which isn’t worth it anymore.
Thank you so much for any tips!

Calc language settings:

Windows 10 language settings:

[mikekaganski]

• The content of the cell is “25-12-20”.
• …
• Now when I format the cell as a date (any form thereof) it merely places a ’ in front of it, making: " '04-01-2021 ".

Do you say that a cell with 25-12-20 after changing cell format becomes '04-01-2021? Exactly this change of content, with “Dec 25, 2020” becoming “Jan 4, 2021”, and two-digit year becoming four-digit year?

Otherwise, see FAQ.

[Haas]

No, sorry for the confusion.

I decided to use 25-12-20 to clearly distinguish the month & day.
I have edited it in post. The year remains digits as well, merely the apostrophe is placed in front, breaking the format seemingly.

[mikekaganski]

It works as designed.

In your cell, the original content was a string. You expect that after you changed cell format, its content changed to a date.

This will not happen. Changing cell format never changes cell content; it may only change existing content representation in cell (if applicable). I.e., it would never change a string 25-12-20 into a number 44190 (which is the internal representation of date 2020-12-25). But if the cell contained a date (actually number 44190) before you changed format, then the same number (still unchanged!) could be represented as, e.g., 25/12/20, or Dec 25, 2020, or whatever representation you chose in the format.

Note that if Calc did what you expected, it would be easy to loose information, which could go unnoticed by user. E.g., in this case, you could lose the specific form of the text, so that after changing the format, information about the original order, number of digits, and separators between them would be lost.

But since the string in the cell could be converted to a date, Calc shows you that, putting the apostrophe ' before the string, which is display-only decoration to help you disambiguate between strings-in-cells-formatted-as-dates and actual dates; it is not a part of actual cell content.

To change the content of your cells, you should use methods that explicitly do that; see the FAQ.

[Haas]

Thanks a lot Mike.

I understand your point.
So the allowed date input format is only reserved for new text being typed into a cell?

I remember being able to do it the way I explained on a previous PC.
The content of the sheets are generated from matlab though, so perhaps something was different there.
Although matlab can not define any meta data for what’s in the cell, just the raw content.

Anyway, I’ve now done it manually as you suggested. I used"Text to columns": Convert Cells from Text to Date | School IT Expert

Thanks again.

[mikekaganski]

So the allowed date input format is only reserved for new text being typed into a cell?

I don’t quite understand the question.

When your cell is formatted as text, the following input is saved in it as text, literally, and is not converted to any number.

After you later re-format the cell, the text already contained there is kept as text, as I described. However, when you enter new content into a cell formatted as date, the new string is converted to numeric value (if possible).

[Haas]

Yes. What I ment was that when entering a date in a “fresh” cell Calc will detect a date (depending on your personal date acceptance patterns) and set the cell format to some default date (depending on your local).

But because my sheet is exported by matlab the cells are defined as text and hence upon opening the sheet Calc does not recognize the date, since it is considered raw text.

Perhaps the past the export from matlab might have been different and hence I could format the cells als dates. Although I don’t think matlab can assign such meta-data, but that’s a topic for another thread.