Issue 97669

Summary: Calc wrongly changes dates
Product: Calc Reporter: Stefan Weigel <stefan.weigel>
Component: open-importAssignee: AOO issues mailing list <issues>
Status: UNCONFIRMED --- QA Contact:
Severity: Normal    
Priority: P3 CC: gkamani2011, issues, jbf.faure, ooo, rainerbielefeld_ooo_qa, villeroy
Version: OOo 2.4.0Keywords: needmoreinfo
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---

Description Stefan Weigel 2008-12-30 19:06:14 UTC
After saving and closing a spreadsheet that contains dates, change the start
date (day zero) in Tools|Options|Calc|Calculate. Then reopen the document. The
dates in the document will change but day zero of the document is the same. This
means data corruption.

Found this bug in OOo 2.4.0 as well as in DEV300_m38

(1) Open a new spreadsheet.
(2) Have a look at Tools|Options|Calc|Calculate|Date (should be set to
"12/30/1899 (default)").
(3) Enter a date into a cell of the spreadsheet. Remember that date.
(4) Save and close this spreadsheet.

(5) Open another new spreadsheet.
(6) Change Tools|Options|Calc|Calculate|Date to "01/01/1900 (StarCalc 1.0)"
(7) Close the spreadsheet, no matter if you save it or not.

(8) Reopen the first spreadsheet.
(9) Compare the date in the cell to the date you remember. The date that is
showing up now is two days before the date you had entered.
(10) Have a look at Tools|Options|Calc|Calculate|Date (it´s still set to
"12/30/1899 (default)").

The date changed by error.

(11) Do some changes to the spreadsheet, but don´t touch the date. For example
type some text in any other cell.
(12) Save and close this spreadsheet.
(13) Reopen the spreadsheet.

Whenever you repeat steps (11) to (13) the date will change by another two days.
Whenever you save your document the dates in your spreadsheet change.
Comment 1 wope 2008-12-30 19:18:58 UTC
if you change the date in the 2nd spreadsheet back to 12/30/1889, and then
reopen the 1st spreadsheet, all seems ok.
Comment 2 Stefan Weigel 2008-12-31 09:25:17 UTC
*** Issue 97609 has been marked as a duplicate of this issue. ***
Comment 3 Stefan Weigel 2008-12-31 09:41:38 UTC
You definitely face data corruption, when you save the document with the wrong
date before setting Tools|Options|Calc|Calculate|Date in the second spreadsheet
back to 12/30/1889.

This issue becomes even more serious, because of yet unreproduced reports from
users, that a virgin installation of OOo 3.0 on Windows Vista under some
circumstances leads to a presetting of Tools|Options|Calc|Calculate|Date to
01/01/1900. In these cases users suffer data corruption even without having
touched any option setting.
Comment 4 khani 2009-09-15 20:41:50 UTC
Using Windows Vista SP2 and Open Office version 3.1.1 OOO310m19 (Build:9240),
replicated the bug by performing the same steps described in Tue Dec 30 19:06:00
+0000 2008 and also performed the interesting addition by  wope Tue Dec 30
19:18:58 +0000 2008.

It seems like open office one file date format is keeping track of the other
file date format, though the other file is not saved but still affecting the
date format of the file that has been save.
  
Comment 5 Rainer Bielefeld 2014-04-20 17:55:05 UTC
NOT Reproducible with "Ooo 3.1.1 German WIN7 Home Premium (64bit) DE [OOO310m19 (Build 9420)]"!

I tried to find something I might have done wrong, and also tried ODF 1, no way, I did not reproduce the problem. I am pretty sure that I did not make a mistake.

Same result with 2.0.2, Document always showed the correct Base date when I checked and so the date in cell was correct.

Even when I open 2 documents from 2.0.2 with different base date with AOO 4.1.0 RC, AOO will use correct base date.

Any Idea why I can't reproduce the bug? Sample documents available?
Comment 6 Andreas Säger 2014-04-20 21:40:55 UTC
I could not reproduce this issue until it hit me. Something added the following entries to my registrymodifications.xcu which then affects the NullDate of all documents where the NullDate is not set explicitly. It changes the default date from 1899-12-30 to 1904-01-01

<item oor:path="/org.openoffice.Office.Calc/Calculate/Other/Date">
<prop oor:name="DD" oor:op="fuse"><value>1</value></prop>
</item><item oor:path="/org.openoffice.Office.Calc/Calculate/Other/Date">
<prop oor:name="MM" oor:op="fuse"><value>1</value></prop></item>
<item oor:path="/org.openoffice.Office.Calc/Calculate/Other/Date"><prop oor:name="YY" oor:op="fuse"><value>1904</value></prop></item>
Comment 7 Rainer Bielefeld 2014-04-21 06:12:31 UTC
I think such an entry is useful and required, but the questions for what we need an answer are
(a) In what cases documents without <table:null-date> tag can be created?
(b) In what cases the additional registrymodifications.xcu entry will be done?

Currently I still do not understand the mechanism. Some of my sample documents contain <table:null-date> tags with appropriate different entries, some do not contain the tag, but always I see the correct date in the spreadsheet when I open the document-
Comment 8 mroe 2014-04-21 09:48:56 UTC
There is no need for any changes.
The date options exist only for compatibility reasons.

Help:
30/12/1899 (default)
Sets 1899-12-30 as day zero.
01/01/1900 (StarCalc 1.0)
Sets 1900-01-01 as day zero. Use this setting for StarCalc 1.0 spreadsheets containing date entries.
01/01/1904
Sets 1904-01-01 as day zero. Use this setting for spreadsheets that are imported in a foreign format.

The main question is, if there are still any file formats that can imported/exported and needs this date settings? If not so then this options should be dropped slightly.
Comment 9 Andreas Säger 2014-04-21 11:00:46 UTC
This has been a major source of error and confusion since many years.
The NullDate is a property that is saved in an ODF document. However, a ususal ODF document has no NullDate. It assumes to be interpreted with day zero 1899-12-30.
A fresh profile does not include any NullDate in registrymodifications.xcu. Once this entry has been added to registrymodificatoins.xcu, it overrides the NullDate for all imported csv files at least. The imported dates are displayed correctly but their underlying values are wrong. When you copy data from the imported csv into a "normal" spreadsheet, the imported dates are off by 4 years and 2 days. Any new ODF file and the existing ones without explicitly set NullDate behave normally.
IMHO, there is no need to import plain text with any NullDate other than the default one. When this happened to me I was absolutely sure that I did not handle any files other than my daily ODF stuff and some imported csv. I have no idea why 1904-01-01 had been registered in my user profile. 

And I do not understand how such a registry change could make any sense.
a) We open ODF or MSOffice formats where it is explicitly saved in the file or defaulting to 1899-12-30
b) We import text data or dBase where the date value is "hard coded" and switching the default value gives only disadvantages.
c) We open some other file format with well known day numbers assuming a different NullDate (Apple Works? historic spreadsheets? don't know). Then we import the file into the speadsheet model and set the right NullDate for this particular model. In the end it is a per-document setting. Why should we modify the registry in this special case?
Comment 10 Gaurav Kamani 2014-09-18 17:42:04 UTC
I tried replicating the bug on Windows 8.1 Pro machine, and also on Mac OS X 10.9.4. On both these systems I ran OpenOffice 4.1.1
The steps were a little different that the reported way (probably due to the different versions of software).

On Windows 8.1
1. Open a new Spreadsheet
2. Then on the Menu Bar we select:
   - Tools
   - Then -> Option 
   - Then from the left pane we expand -> OpenOffice Calc (by clicking the     
     little '+' sign next to it)
   - Then -> Calculate
   - Then we find 'Date' on the top right pane, and confirm that "12/30/1899  
     (default)" is selected. If not, we select it.
   - Then we hit -> OK at the bottom of the dialog box.
3. Enter a date into a cell of the spreadsheet. We will need to remember that 
   date.
4. Then we save and close this spreadsheet.

5. Open a new spreadsheet
6. Then on the Menu Bar we select:
   - Tools
   - Then -> Option 
   - Then from the left pane we expand -> OpenOffice Calc (by clicking the     
     little '+' sign next to it)
   - Then -> Calculate
   - Then we find 'Date' on the top right pane, and and now select 01/01/1900 
     (StarCalc 1.0)"
   - Then we hit -> OK at the bottom of the dialog box.
7. Then we close this spreadsheet (Saving it or not saving it will not affect the outcome)

8. Then we reopen the first saved spreadsheet
9. When we compare the date it will be two days behind.

Another anomaly:

- We make some changes to the spreadsheet, but not to the date. For example
  type some text in any other cell.
- Save and close this spreadsheet.
- Reopen the spreadsheet.
After we reopen, the date decreases by two days again.

Similar process is undertaken in MAC OS X as well.
The only difference is in a part of step (2) and step (6).
Modification:

-> On the Menu Bar we select:
   - OpenOffice
   - Then -> Preferences
     (This will reveal the same dialog box as "Options" will do on Windows.

So the bug replicates as "ooo@wolfachtal.de" points out.

Also, opening a new spreadsheet and following the process and changing the date back to "12/30/1899  
(default)" will fix this issue.
Comment 11 Andreas Säger 2014-09-30 18:37:11 UTC
Of course, this issue will never be reproduced. It occurs every now and then when something writes an explicit null date into registrymodifications.xml which IMHO should never happen because the null date is either written into the document unless it depends on the specific file format to be loaded.

Today's issue on the user forum: https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=72644
Comment 12 Andreas Säger 2014-09-30 20:03:46 UTC
How to reproduce the problem easily:

Add or edit the Calculate/Other/Date section so it declares 1904-01-01 as default NullDate:

<item oor:path="/org.openoffice.Office.Calc/Calculate/Other/Date">
<prop oor:name="DD" oor:op="fuse"><value>1</value></prop>
</item><item oor:path="/org.openoffice.Office.Calc/Calculate/Other/Date">
<prop oor:name="MM" oor:op="fuse"><value>1</value></prop></item>
<item oor:path="/org.openoffice.Office.Calc/Calculate/Other/Date"><prop oor:name="YY" oor:op="fuse"><value>1904</value></prop></item>

Open some existing spreadsheets with dates. By default these documents _should_ not have any NullDate setting which _should_ default to 1899-12-30 but now it defaults to 1904-01-01 and all the dates are 4 years + 2 days ahead.