[Solved] Dates change mysteriously?

Discuss the spreadsheet application

[Solved] Dates change mysteriously?

Postby jackrcook » Tue Sep 30, 2014 8:01 pm

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.
Attachments
sample.ods
(32.41 KiB) Downloaded 67 times
2014_Dates.ods
(12.52 KiB) Downloaded 56 times
Last edited by jackrcook on Wed Oct 01, 2014 12:34 am, edited 1 time in total.
Open Office 4.1.5 ~ Mac OS 10.14.5 Mojave running on an iMac
jackrcook
 
Posts: 211
Joined: Sun May 25, 2014 11:08 pm

Re: Dates change mysteriously?

Postby RusselB » Tue Sep 30, 2014 8:25 pm

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.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5391
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Dates change mysteriously?

Postby Villeroy » Tue Sep 30, 2014 8:42 pm

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   Expand viewCollapse view
<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
Last edited by Villeroy on Fri Apr 01, 2016 11:40 am, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dates change mysteriously?

Postby RusselB » Tue Sep 30, 2014 9:07 pm

Nice find Villeroy
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5391
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Dates change mysteriously?

Postby Alex1 » Tue Sep 30, 2014 9:29 pm

2014_Dates.ods uses StarCalc's date standard which differs by 2 days from the Calc standard: Tools>Options>OpenOffice Calc>Calculate.
AOO 4.1.5 & LO 5.0.6 on Windows XP & 7
Alex1
Volunteer
 
Posts: 604
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands

Re: Dates change mysteriously?

Postby jackrcook » Tue Sep 30, 2014 9:41 pm

Villeroy wrote: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   Expand viewCollapse view
<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.
Open Office 4.1.5 ~ Mac OS 10.14.5 Mojave running on an iMac
jackrcook
 
Posts: 211
Joined: Sun May 25, 2014 11:08 pm

Re: Dates change mysteriously?

Postby Villeroy » Tue Sep 30, 2014 9:59 pm

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

jackrcook wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dates change mysteriously?

Postby Villeroy » Tue Sep 30, 2014 10:28 pm

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dates change mysteriously?

Postby jackrcook » Tue Sep 30, 2014 11:05 pm

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."
Open Office 4.1.5 ~ Mac OS 10.14.5 Mojave running on an iMac
jackrcook
 
Posts: 211
Joined: Sun May 25, 2014 11:08 pm

Re: Dates change mysteriously?

Postby Villeroy » Tue Sep 30, 2014 11:24 pm

jackrcook wrote: 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dates change mysteriously?

Postby jackrcook » Wed Oct 01, 2014 12:33 am

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.
Open Office 4.1.5 ~ Mac OS 10.14.5 Mojave running on an iMac
jackrcook
 
Posts: 211
Joined: Sun May 25, 2014 11:08 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests