In my sheet I have the only one cell with 7/27/2024, I want to make this cell in date format with format MM/DD/YYYY but when I do it, Calc add apostrophe to the cell: '7/27/2024
I also tried manually set 07/27/2024 but the same problem…
Format look the same, why Calc can’t parse it?
P.S. But if I just copy text from my cell to other cell then Calc automatically recognizes my text as date…
This is my file
dateExample.ods (9.8 KB)
You can see that Calc can parse it, exactly because Calc sets that apostrophe, telling you: “I see a string in your formatted cell, and that string is indistinguishable from a proper date; so let me provide you a visual clue, so that you also see, that that is not a proper date, but a string”.
Formatting cells never ever changes cells’ data.
See FAQ.
You did not type that date into a cell in the first place, did you? You rather imported the date from a text file or from clipboard.
When importing plain text, you get the following dialog, where you can set the import locale (aka language) to US-English because of the MDY dates and decimal points. Option “Detect special numbers” should be checked anyway.
you are right, I copy from another file and past to my file.
But why I can’t override format, apply date format with force?
You can. And you do. What you can’t is to change the data by applying format - and you need to realize the difference between data (and its type - be it numerical, including date, or textual), and its format.
Again: formatting never changes data, only its representation on screen. But since you wrote that reply, I wonder why explain it again, when you obviously ignored the explanation and the link above.
But if I copy text of my cell and paste to another one then Calc set new call with Date format automatically!
“formatting never changes data”
Yeap. When copy text of cell and past to another cell, I don’t change data, right? It was 7/27/2024 and will be 7/27/2024… But format will be different… Why?
If the communication contains something of a meaning like
We need the medicine by 8/7/25 at the latest.
this can cost a life If not both parties know about the stubborn regional preferences on the other side. If they knew, dates would anyway be exchanged in ISO 8601 (delimited by dashes and -in critical cases- UTC to disambiguate the date).
Different settings concerning the “nulldate” may not occur frequently nowadays, but are still possible.
When you copy text and paste it into a cell, you are changing content of a cell; at the moment of content entry, Calc recognizes what you entered (if that’s some number, like date, or just a text), and stores the data as recognized at that moment. It is NOT formatting, you simply don’t try to realize; it’s data type.
Then you may apply formatting as much as you wish; that will not change the data type.
I see, Calc recognized wrong way… How to set data type to date with MM/DD/YYYY by force? Why I can’t set it this way? Why Calc just add apostrophe? Here people use the same solution, but it works for their case, why?
You can apply any number format after you have imported correct values. In order to interpret lines of text correctly, you’ve got to specify some details. No computer program can decide if 2/3/2025 refers to 2nd of March or 3rd of February.
Because the cell contains a text.
Try this: '0123 enters the literal text “0123” into a cell and no number format will ever convert this text into number 123. Same with dates, times, booleans, percent.
Because at the moment of that data entry, no proper data was provided for Calc to recognize.
It is explained in the FAQ I gave.
Because that wasn’t Calc.
The TEXT string 7/27/2024 has 3 numbers. Any computer is stupid and can see only 3 numbers. What is therein a day, a month, a year? You can see the year 2024 cause 4 digits, you can see the 27 as a day cause greater than 12, you cannot see if the 7 is the day or is the month. Are you able to separate the DATUM 10|11|12? So, separate by a simple formula to year, day, month manually:
00000_dateExample_returno_122211.ods (16.8 KB)
“What is therein a day, a month, a year?”
But I click to cell and pointed out… Why this way doesn’t works?
You have text consisting of individual characters/digits, called a “string”.
You need to convert it into a decimal number. Today, on 2024-07-22, that’s the number “45860,” and if “0” starts on 1999-12-30, that’s the number. Only then, you can format this number as a date of any type you want and return into a number.
