OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-formula message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: Re: [office-formula] Calculation Settings

Andreas J. Guelzow wrote:
> On Fri, 2009-05-15 at 18:01 +0000, Eric Patterson wrote:
>> - Table:null-date represents a date.  Are conforming applications required to support any possible value for this setting?
>> I believe that settings that are most portable and produce the best interoperability to be:
>>         table:null-date ="1899-12-30"

That's probably true as being the MOST portable.  But the hope was to 
support other options.

> The more times I read the 1.2 draft the less I understand how this is
> useful at all. 
> How often are users going to use the date "0"? 

The "null-date" value is the beginning-of-the-epoch value. It's not that 
they use "date 0", it's that when you see "7000", you know what the 
starting point is.

> There may be an implicit assumption that the number 100 represents a
> date 100 days after the null-date but
> (a) I don't see that stated anywhere
> (b) Not all current spreadsheet programs fulfil this assumption.

It's there.   It's worded slightly more oddly, because of the many 
spreadsheet apps that get 1900 leap-years wrong.

In 3.3 is says: "table:null-date (1899-12-30): Defines the beginning of 
the epoch; a numeric date of 0 equals this date."

> There is some specification relating to this in the openformula draft
> (4.2.2) but that seems to be restricted to formulas from that name
> space. Even in the draft, applications are allowed to reproduce the
> "1900 is calculated as a leap year" situation. Since we don't have a
> calulation setting reflecting that, table:the null-date only tells us
> something about the date->number conversion in OpenFormula for the date
> range from the null-date until 1900/2/28 unless the null date is after
> 1900/2/28.

OpenFormula only applies to OpenFormula formulas, of course.  All specs 
only apply to users of the spec :-).

Section 4.2.2 says this:
"Date and DateTime
A Date is a subtype of number; the number is the number of days from a 
particular date called the epoch. Thus, a date when presented as a 
general-purpose number is also called a serial number. This 
specification does not specify the exact value of the epoch, but 
implementations shall support all dates from 1904-01-01 through 
9999-12-31 (inclusive), with correct calculations. Portable spreadsheet 
files shall not assume any particular epoch values. Since dates are 
simply numbers, they can be added, subtracted, and so on like other 
Numbers. Subtracting one date from another produces the number of days 
between the dates.
Implementations of formulas in an OpenDocument file shall use the epoch 
specified in the  table-null-date attribute of the 
<table:calculation-setting> element, and shall support at least the 
following epoch values: 1899-12-30, 1900-01-01, and 1904-01-01."

So here's how I read the spec: for dates starting at 1904-01-01, you 
MUST correctly calculate dates from the beginning of the epoch (0).   It 
doesn't _require_ that dates be calculated correctly BEFORE that date, 
but it DOES permit it.  E.G., Excel doesn't handle 1900 incorrectly, and 
need not change to implement the spec.  OpenOffice does 1900 correctly, 
and it doesn't need to change either.  Mac spreadsheets that use 
1904-01-01 as the epoch can have trouble with ANY date before then. 
This means that dates before 1904-01-01 (esp. in year 1900) are not 
necessarily exchangeable per the spec, but this _IS_ actually the 
current situation.  The important point is that we don't REQUIRE 
applications to do it wrong.

I agree with Eric Patterson, as I noted above... "1899-12-30" is the 
most likely value for null-date.  Which is why it's the default.

--- David A. Wheeler

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]