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

*Subject*: **Re: [office-formula] Calculation Settings**

*From*:**"Andreas J. Guelzow" <aguelzow@math.concordia.ab.ca>***To*: office-formula@lists.oasis-open.org*Date*: Fri, 15 May 2009 16:04:50 -0600

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

**Follow-Ups**:**Re: [office-formula] Calculation Settings***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

**Re: [office-formula] Calculation Settings***From:*robert_weir@us.ibm.com

**References**:**[office-formula] Calculation Settings***From:*Eric Patterson <ericpa@exchange.microsoft.com>

**Re: [office-formula] Calculation Settings***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

**RE: [office-formula] Calculation Settings***From:*Eric Patterson <ericpa@exchange.microsoft.com>

**RE: [office-formula] Calculation Settings***From:*"Andreas J. Guelzow" <aguelzow@math.concordia.ab.ca>

**Re: [office-formula] Calculation Settings***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

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