Day zero is 1904-01-01 for new spreadsheets

Villeroy · February 27, 2011, 1:06pm

Ubuntu 10.4, LibO 3.3.1.
Since many years I use some default template for new Calc documents. Today I reset this default template in the templates organizer.
Now every new document gets 1904-01-01 as day zero.

This is the respective entry in content.xml:
<table:null-date table:date-value="1904-01-01"/>

This does not happen in OOo 3.3. where I performed the same steps.
The new document from scratch does not have any table:null-date which defaults to 1899-12-30.
Could not find bugs with Null-Date nor "1904".

Hope it's not a new feature.

Andreas

Guy_Voets · February 27, 2011, 6:14pm

Ubuntu 10.4, LibO 3.3.1.
Since many years I use some default template for new Calc documents. Today I
reset this default template in the templates organizer.
Now every new document gets 1904-01-01 as day zero.

This is the respective entry in content.xml:
<table:null-date table:date-value="1904-01-01"/>

This does not happen in OOo 3.3. where I performed the same steps.
The new document from scratch does not have any table:null-date which
defaults to 1899-12-30.
Could not find bugs with Null-Date nor "1904".

Hope it's not a new feature.

Andreas

I remember I used to import dates of birth in Excel files made in
Windows, and on my Mac everybody got 4 years minus one day younger -
or somthing like that. Excel on Windows used another 'null date' than
Excel for Mac. There was an option in the preferences to choose
between 01-01-1904 and the Windows date (01-01-1900 or 31 / 31-12-1899
- that I don't remember).
The problem you found seems similar to this.

Looking a bit further, I found in LibO's Prefs: LibO Calc > Calculate

Date the three possibilities (Standard, StarOffice Calc 1.0

(01-01-1900), 1-1-1904). By default, the date is set to 31-12-1899.

When you make your template, you can choose your start date here.

HTH

NoOp · February 27, 2011, 6:19pm

What are you showing for:
Tools>Options>LibreOffice Calc|Calculate|Date
The default should be 12/30/1899 (or in your case 1899-12-30).

Pedro_Lino · February 27, 2011, 7:02pm

This is puzzling:

if day Zero is 0-Jan-1900 and day 1 is 1-Jan-1900 for Excel and day Zero is
30-12-1899 and day 1 is 31-12-1899 for both LibO 3.3.1 and OOo Dev101, how
come =now() returns the same value in both spreadsheets?

Villeroy · February 27, 2011, 8:01pm

Am 27.02.2011 20:02, plino wrote:

This is puzzling:

if day Zero is 0-Jan-1900 and day 1 is 1-Jan-1900 for Excel and day Zero is
30-12-1899 and day 1 is 31-12-1899 for both LibO 3.3.1 and OOo Dev101, how
come =now() returns the same value in both spreadsheets?

Because of the day with no sunrise. 1900-02-29 is a valid date in Excel. In reality 1900 was not a leap year. Calc's 1899-12-30 is Excel compatible for all dates >= 1900-03-01.

Pedro_Lino · February 27, 2011, 10:00pm

Very interesting, Andreas.

Today I learned something about leap years thanks to you

http://kalender-365.de/leap-years.php

I'm glad I don't have any time series of data going back to the 19th century

Villeroy · February 27, 2011, 8:05pm

Am 27.02.2011 19:18, NoOp wrote:

What are you showing for:
Tools>Options>LibreOffice Calc|Calculate|Date
The default should be 12/30/1899 (or in your case 1899-12-30).

No, the default for all brand new documents (not from template) is 1904-01-01 which causes a lot of trouble when you copy&paste dates between documents.

Villeroy · February 27, 2011, 8:15pm

Am 27.02.2011 19:14, Guy Voets wrote:

When you make your template, you can choose your start date here.

HTH

As already mentioned that I use to use my own default template. This occured to me during a test with a brand new document.
Nevertheless, day zero must not be 1904-01-01 for brand new documents. I consider this to be a bug.

T_R_Valentine · February 27, 2011, 10:48pm

I believe it is the default for the Mac, for technical reasons.

NoOp · February 27, 2011, 11:25pm

Possibly a profile or locale issue? I just checked LO 3.3.1 on 4
different machines and the default is 12/30/1899[1]. Even checked the
Windows version in a VM. To test I first verified that was the default
setting in each, then did:

The cell is now showing as 12/30/99 and if I click on the cell I see
12/30/1899 in the formula bar. I also closed out all LO's and restarted
to see if there was any change & they remained the same.
Also set one to 01/01/1904 & tested. Closed that out and it came back
up as 01/01/1904 OK.

You might try renaming ~/.libreoffice.org and let LO build a new profile
to see if that makes any difference.

Sorry, forgot to ask in the first post: are you using the LO deb file or
an Ubuntu PPA install? All of my tests are with standard LO deb installs
from libreoffice.org/download.

[1] Your date display will of course be different as I'm using standard
US English.

Villeroy · February 27, 2011, 11:48pm

Am 28.02.2011 00:25, NoOp wrote:

You might try renaming ~/.libreoffice.org and let LO build a new profile
to see if that makes any difference.

Resetting the profile cures the problem. No, I don't want to reset my user profile. I'm going to reestablish my default template.

Thanks,
Andreas

Tom_Davies · February 28, 2011, 10:29am

________________________________
From: Andreas Säger <villeroy@t-online.de>
To: users@libreoffice.org
Sent: Sun, 27 February, 2011 23:48:01
Subject: [libreoffice-users] Re: Day zero is 1904-01-01 for new spreadsheets

Am 28.02.2011 00:25, NoOp wrote:

You might try renaming ~/.libreoffice.org and let LO build a new profile
to see if that makes any difference.

Resetting the profile cures the problem. No, I don't want to reset my user
profile. I'm going to reestablish my default template.
Thanks,
Andreas

Hi Andreas,
Now that you found resetting the profile cures the problem it should be possible
to copy back some of the things from your renamed profile into the new one.
That gets your old settings and stuff back while helping pinpoint exactly which
part of your profile was causing the problem. Copy&paste is great but if you
are in linux then rsync might be better because it keeps permissions and stuff
exactly the same. I don't think Windows has something that sophisticated tho so
copy&paste would have to do.
https://help.ubuntu.com/community/rsync
Good luck and regards from
Tom

NoOp · February 28, 2011, 5:00pm

You may be able to check/modify the
~/.libreoffice/3/user/registrymodifications.xcu file. From my testing
yesterday I see the changes in mine that refer to the Date test:

oor:name="CaseSensitive"
oor:op="fuse"><value>true</value></prop></item><item
oor:path="/org.openoffice.Office.Calc/Calculate/Other/Date"><prop
oor:name="DD" oor:op="fuse"><value>30</value></prop></item><item
oor:path="/org.openoffice.Office.Calc/Calculate/Other/Date"><prop
oor:name="MM" oor:op="fuse"><value>12</value></prop></item><item
oor:path="/org.openoffice.Office.Calc/Calculate/Other/Date"><prop
oor:name="YY" oor:op="fuse"><value>1899</value></prop></item><item
oor:path="/org.openoffice.Office.Calc/Calculate/Other"><prop

Note the "name="YY" oor:op="fuse"><value>1899</value>" - this value
changes when I make the changes (1904) in Tools|Options.

The file does not open well in gedit, so I used (U)OOo to open to view &
then used gvim to modify. I changed the 1899 value to 1904, saved, and
then opened Calc - the date value was then set to 1904 in the spreadsheet.

The usual warnings apply (backup, use at your own risk, etc)

Gary