Re: [office-formula] Calculation Settings

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

On Sat, 2009-05-16 at 23:19 -0400, David A. Wheeler wrote:
> robert_weir@us.ibm.com wrote:
> > Here's what I'm thinking:
> > 
> > We have an abstract machine that does operations on dates.  Dates, when 
> > stored, are stored as ISO 8601 strings.  We also define explicit and 
> > implicit ways of converting numeric data into dates.  The implict coercion 
> > method has one or more parameters, one of which is the date basis.
> > ...   I don't think
> > we should care.  What we really need to define is the type conversion. 
> > 
> > If we state it along those lines (conceptually -- obviously the text would 
> > need to be more fully articulated) would that do the job?
> 
> Well, they really ARE numbers.  For example, ISNUMBER() must return 
> TRUE() for a date or datetime value.   I know of no spreadsheet 
> applications that has a true separate date type; I believe all 
> applications simply use numbers, and whether or not it's a date or time 
> or datetime is simply a formatting property of a particular cell.  ODF's 
> storing of the 'current cell value' as an ISO date is still very nice; 
> it partly isolates spreadsheets from some of the worst nastiness of 
> epoch values.
> 
> If describing the operations as a separate type would be the best way to 
> express what's required, then it might still be fine to word it as a 
> "separate" type, if worded carefully.  Certainly there are a lot of 
> languages with such types which could be used as a model.  Of course, 
> good wording is always the trick :-).  Part of the reason the current 
> text is so awkward is because it's trying to work around the obvious 
> problem: Some applications (intentionally) calculate 1900's leap-year 
> incorrectly, while others do it correctly.
> 
> If the current text isn't as clear as it should be, then by all means, 
> please, propose an alternative!

I think switching to a separate data type would cause the problem that
there are lots of spreadsheet documents out there that do calculations
with these dates as if they are numbers and so defining the separate
data type would require to explain exactly how calculations work.

My problem with the current formulation is that we:
(a) say one thing about null-date *that it is the date whose serial
number is 0)
(b) are mute about the progression through dates, ie. that the serial
number of the next day must be one larger (although of course everybody
_assumes_ that to be the case).
(c) in OpenFormula suggest that under certain circumstances we violate
(a) and
(d) that the non-specificity of a skip (or the absence of a skip) in
serial number from 1900/2/28 to 1900/3/1 is unspecified.

Assuming we want to be able to be sure about the serial numbers of dates
after 1900/3/1, I think we should
(1) have a calculation setting that gives the serial number for 1904/1/1
(or any other agreeable date after 1900/3/1)
(2) have a statement that for every date after 1904/1/1 the difference
in serial numbers is equal to 1 more than the number of days properly in
between those dates.

Andreas




-- 
Andreas J. Guelzow
Concordia University College of Alberta