Apache OpenOffice (AOO) Bugzilla – Issue 85362
Calc: Default Formatting ruins dates
Last modified: 2013-01-29 21:52:01 UTC
When a date is entered into a Calc cell and then Default Formatting is selected, the date turns into an integer. For example, entering "2008-7-7" and then selecting Default Formating causes the cell to display "39636". I do realize that Calc stores dates as integers internally, but if that is the case then the Default Formating for cells that are displayed as dates should be to display the data as a date. The current behaviour is confusing and worrying as it causes users to believe that data is corrupted.
Can reproduce it in OOo3.0_RC1 on WinXP.
This is confusing for sure, but is probably considered by design. When setting any cell to Default Formatting it will set the cell as a number and not a date. Default format is always a number. If would be better if Calc could recognize a date as a date and set the cell to default date format.
I'm trying to enter some part numbers example 11-0011 and calc changes it to a date of 11/01/11 which is 11/01/2011. I cannot see why.
It would be nice if Calc just showed the data as it was entered. If the user enters "03102008" and Calc reformats it to "2008-10-03", then the user clicks Default Formating, then Calc should show "01102008" and not "39874".
There was an error in my last comment it should read: It would be nice if Calc just showed the data as it was entered. If the user enters "03102008" and Calc reformats it to "2008-10-03", then the user clicks Default Formating, then Calc should show "03102008" and not "39874".
This is more of a UI bug. When selecting "default" format for cells, the term "default" is taken by users to mean "default for the type I specified" (i.e. if the cell is a date, it should be "default date format for my country). Unfortunately, in a spreadsheet, "default" simply means "number, integer". So the "Default formatting" option should be called "Reset to default spreadsheet format : integer number". There is no silver bullet. Date operations are made on integers. Not 2 individuals will agree on what is a default format for a date depending on what type of use for that date they want.
> This is more of a UI bug. When selecting "default" format for cells, the > term "default" is taken by users to mean "default for the type I > specified" (i.e. if the cell is a date, it should be "default date > format for my country). Unfortunately, in a spreadsheet, "default" > simply means "number, integer". So the "Default formatting" option > should be called "Reset to default spreadsheet format : integer number". > While I agree that that "bug" is a UI issue, the fix in my opinion is not to change the text of the UI but rather to bring the application's behaviour in line with what the UI suggests. > There is no silver bullet. Date operations are made on integers. Not 2 > individuals will agree on what is a default format for a date depending > on what type of use for that date they want. > No two individuals have to agree! Calc should use the format used when entering the data. For instance, if the user entered "2010-02-11" then Calc should use yyyy-mm-dd and if the user entered "27.10.2010" then Calc should use dd.mm.yyyy. Of course there is the problem of ambiguity when the user enters "10-12" but that problem is currently assumed in Calc anyway, so Calcs current behaviour (barring any open issues) should be preserved in these cases. That is, if Calc currently recognizes "10/12" as dd/mm/yy then that behaviour should remain. But currently, if the user enters "10-12" and pressed Enter, he sees "10/12/10" in the cell. If he then chooses "Default Formatting" the cell should display "10-12", not "40463" as it currently does.
If an American sends me a spreadsheet, he will have entered it a date in US format... if I open it, in order for me to understand it, I want it displayed in a European format. If he enters MM/DD/YY, I want it displayed DD/MM/YYYY... we don't have to agree... My default "date format" may not be the same as your default "date format"... But a spreadsheet's default format for data is numbers... or else it would be a database... not a spreadsheet. :)
> If he enters MM/DD/YY, I want it displayed DD/MM/YYYY... we don't have > to agree... Actually, we do agree. I cannot understand the "least significant unit in the middle" date format. Do those users have their clocks set to HH:SS:MM as well? > My default "date format" may not be the same as your default "date format"... > But a spreadsheet's default format for data is numbers... or else it would > be a database... not a spreadsheet. :) With this in mind, I agree that the "Default Format" should not necessarily be the format that the spreadsheet was created in. Rather, the "Default Format" should be the preferred format configured for the user (or, lacking that, the format configured for the user's locale). Therefore, this bug may depend upon bug 5556 (date format in the formula bar not matching that which is set for the OS - unexpected behaviour) and bug 72229(add an option to configure the date format - feature request).