[Solved] Dates change mysteriously?

[jackrcook]

The “sample.ods” file has 3 sheets. “PayChecks” sheet has a date input cell C1. All calculations are made doing a vlookup using the cell C1.

Any change I make to a date entry in any of the 3 sheets works to update the calculations just fine at that moment.

However, if I save and re-open the file, the dates are all two days off.

I have included a brand new file from which I can copy and paste the date values I actually want only to find that they are all two days off after a save and reload the “Sample.ods” file I copy them to.

If I re-load the “2014_Dates.ods” file the dates are exactly as saved (they do not mysteriously change by two days).

This only happens for me in the “Sample.ods” file. How can this happen and how do I fix it?

Thanks for any clues.

[RusselB]

No idea how this happened or how to fix it, but I did a copy of the dates from your 2014_Dates.ods and pasted that, using Paste Special (only Dates & Times checked) to the sheet Rebecca of Sample.ods. The resulting dates, were one day further into the future, in comparison to the dates which were previously there.
I confirmed this by earlier ensuring that the dates on Rebecca's sheet matched the dates from Manuel's sheet, then comparing them again after pasting to Rebecca's sheet.

Definetly weird.

[Villeroy]

Find your user profile. It is a folder somewhere on your system, may be /Users/<user name>/Library/Application Support/OpenOffice/4/user or some similar path.
Close the office suite.
Make a backup copy of file registrymodifications.xcu
Load the file into your favourite text editor.
Find the section

Code: Select all

<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>1900</value></prop></item>

Delete the whole section or change the 3 numbers DD = 30, MM = 12 and YY = 1899
Save the file.
Restart the office.

https://issues.apache.org/ooo/show_bug.cgi?id=97669

[RusselB]

Nice find Villeroy

[Alex1]

2014_Dates.ods uses StarCalc's date standard which differs by 2 days from the Calc standard: Tools>Options>OpenOffice Calc>Calculate.

[jackrcook]

Find your user profile. It is a folder somewhere on your system, may be /Users/<user name>/Library/Application Support/OpenOffice/4/user or some similar path.
Close the office suite.
Make a backup copy of file registrymodifications.xcu
Load the file into your favourite text editor.
Find the section

Code: Select all

<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>1900</value></prop></item>

Delete the whole section.
Save the file.
Restart the office.

https://issues.apache.org/ooo/show_bug.cgi?id=97669

Villeroy,
Before I execute your instructions, please comment on why the strange behavior does not occur with the "2014_Dates.ods" file? I don't want to get in deeper. The link you provided appears to document this strange behavior back to 2008. Why is it still an issue 6 years later?
I tried to find the folder you referenced using a cut and paste into spotlight. Can't find it. Any suggestions on that issue?
Thanks again for your help.

[Villeroy]

In the OpenOffice options dialog you find several path settings. They give you a hint about the location of the ..../user folder.

Before I execute your instructions, please comment on why the strange behavior does not occur with the "2014_Dates.ods" file?

When some ODF document has some base date (null date) explicitly set, the registrymodifications.xcu won't have any effect.

If registrymodifications.xcu does not contain any section <item oor:path="/org.openoffice.Office.Calc/Calculate/Other/Date">...</item> this is perfectly OK. In my honest opinion there should not be any such entry in that file. If this is the case, you should reset the base date of your Open Document spreadsheets. Only in very rare cases it may be useful to set any base date other than 1899-12-30.

The link you provided appears to document this strange behavior back to 2008. Why is it still an issue 6 years later?

Nobody really tries to reproduce the problem.

[Villeroy]

Indeed, 2014_Dates.ods is set up with base date 1900-01-01. Change that, save the file and everything should be fine. The date is stored in the document. If nobody changed the date manually, an anchestor of this file may have been a very old StarOffice file or something else with base date 1900-01-01

[jackrcook]

Villeroy,
I just found an AOO preference page dealing with "calculate" and three date choices. I may have caused my own problem by changing the default to StarCalc in the past while trying to see what it affected. I then must have forgotten about it. Now, I am trying to get "sample.ods" to work as originally intended by setting it to default. How do these preferences interact with the file edits you recommended? I have TimeMachine backups that I probably could draw on that precede my monkeying with the attached settings.
What do you think I should try next?
Thanks
PS I attempted to attach a screen shot of the preference page, but it was "too big."

[Villeroy]

How do these preferences interact with the file edits you recommended?

There had been reports about spontanious base date changes to 1904-01-01 although no such setting was saved within the document itself. This also happened to me and I found a 1904-01-01 setting in my registrymodifications.xcu. This setting was used when there was no setting explicitly stored in the document. Most ods documents do not have this setting stored which _should_ default to 1899-12-30.
When 2 documents use different null dates, say 1900-01-01 and 1899-12-30, they map the same numeric cell values to different date values (or the same dates to different values). When you format the number 40,000 as a date, you see 2009-07-08 in one document and 2009-07-06 in the other document because 2009-07-06 is day number 40,000 since 1899-12-30 while 2009-07-08 is day number 40,000 since 1900-01-01. Internally all spreadsheet programs handle dates as day numbers.
When you copy or reference cells between the 2 files only the true cell values are considered but the true cell value 40,000 is interpreted differently as 2009-07-08 in one file and 2009-07-06 in the other.
When you copy dates from some very old spreadsheet files it may be necessary to adjust the null date so the same numeric cell values refer to the same dates in both files.

[jackrcook]

I got back to square one with everything working as originally desired. TimeMachine and putting the AOO preference back to the default date setting.
No need to mess with my AOO profile. Maybe next time.
Again, thanks to all for the help.