Re: [office-formula] Calculation Settings

["Andreas J. Guelzow" <aguelzow@math.concordia.ab.ca>]

On Fri, 2009-05-15 at 17:48 -0400, David A. Wheeler wrote:
> 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.

You are saying "starting point" but that is not what the draft is
saying. The draft standard describes only a single point not how it
continues.
> 
> > 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."

But what does that really mean? You are assuming that "beginning of the
epoch" means that the value corresponding to one day later is 1 larger.
It doesn't seem to say that anywhere.
> 
> 
> > 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.
> 

Am I the only one you sees at least one contradiction in all of this:

Part 1 8.4.1.1 says: 
The <table:null-date> element specifies the null date for a spreadsheet
document. The null date is the date that results in the value “0” if a
date value is converted into a numeric value.

part 2 4.2.2 says: Portable documents should use the epoch date
1899-12-30 to compensate for serial numbers originating from
applications that include a 1900-02-29 leap day in their calculations.

In the context of 4.2.2 this seems to say that 1899-12-30 is not the
true null-date since we are "compensating" to have matching values after
1900-3-1. 

If you want to be able to match serial numbers after 1900-3-1 and you
don't have a flag that indicates whether you have a serial number skip
between 1900-2-28 and 1900-3-1, then you can't fix the serial number at
a date prior to 1900-3-1.

I think what one really should want is a specification of the serial
number matching 1904-1-1 and a statement how days and changes in serial
numbers are related.

Andreas



-- 
Andreas J. Guelzow, PhD, FTICA
Coordinator, Mathematical & Computing Sciences
Concordia University College of Alberta