RE: [office-formula] Year 1900 bug

["Dennis E. Hamilton" <dennis.hamilton@acm.org>]

I assert that your explanation is equivalent to use of a particular origin
(the same as the Win32 Excel default) and a particular calendar mapping in
which the value 60 does not map to any date.  This mapping is used, of
course, when a Number type cell value is to be presented in date format.

That is certainly one way to use the same origin and a slightly-modified
mapping that agrees with the one of Win32 Excel with one exception.  I think
it is up to ODF 1.2 Part 1 to establish whether that particular combination
is available, is the default, or is not recognized.  It is an interesting
case.

(I have no interest in advancing a particular choice, just that OpenFormula
be specified in a way that is neutral about it and that arithmetic on values
representing ordinal time works as expected whatever the global origin date
and calendar mapping happen to be.  I presume that some OpenFormula-hosting
specifications might allow multiple ones to be supported in some way.)

 - Dennis

  PS: It seems to me that the current definition of datedif is a little
loose, although the illustrated cases are informative.  I think the key is
that it depends on what the understood origin and calendar mappings are in
order to provide a proper result.  There probably needs to be a caveat that
it should count only actual calendar days traversed in the case of "d", and
I suppose there needs to be some adjustments for how spans of months and
years work, so there is specificity about the edge cases.
  I presume datedif will fail if given a Numeric type (cast to Integer) that
is a number having no mapping to a calendar date (i.e., 60 for the case in
hand).
  Does datedif presume the notions of month and year are those of the
Gregorian calendar, as embodied in ISO 8601?

-----Original Message-----
From: Andreas J Guelzow [mailto:aguelzow@math.concordia.ab.ca] 
http://lists.oasis-open.org/archives/office-formula/201001/maillist.html
Sent: Sunday, January 24, 2010 22:35
To: office-formula@lists.oasis-open.org
Subject: RE: [office-formula] Year 1900 bug

On Sun, 2010-01-24 at 23:33 -0700, Andreas J. Guelzow wrote:

> Note that =datedif(A2,A4,"d") results as expected in 1 since these are
> the true date differences!
> 

This was a typo and should have been 
=datedif(A1,A3,"d") results as expected in 1.


-----Original Message-----
From: Andreas J. Guelzow [mailto:aguelzow@pyrshep.ca] 
http://lists.oasis-open.org/archives/office-formula/201001/msg00077.html
Sent: Sunday, January 24, 2010 22:34
To: office-formula@lists.oasis-open.org
Subject: RE: [office-formula] Year 1900 bug

On Sun, 2010-01-24 at 15:16 -0800, Dennis E. Hamilton wrote:
> Your proposed wording is certainly an improvement.

I am not sure about that. But perhaps I don't even understand what hte
"Year 1900 bug" is all about, since we do not specify the exact serial
numbers anyways.

Let me explain in more detail and at the example of Gnumeric.

I understand that there has never been a Feb 29, 1900 since 1900 is
divisible by 4 but also by 100 and not be 400.

So Gnumeric knows very much that there is no such date either.
Nevertheless this doesn't stop Gnumeric to have the same date serial
numbers as Excel since this is required for compatibility. FOr the
compatibility you do not need to believe in a Feb 29, 1900.

Suppose you start a recent version of Gnumeric and enter in cell A1
1900/2/28. Gnumeric of course recognizes this as a date and converts it
into a serial number (59), showing it nevertheless as a date
(1900-Feb-28).

Now if you enter in A2 the formula =A1+1 then it will calculate the sum
of 59 and 1 resulting in 60. Since the form of the sum (reference
formatted as a cell plus a number) indicates a date calculation,
Gnumeric formats the result cell as a date. Since there is no date with
serial number 60 the cell shows ########. 

If we enter in A3 the formula =A2+1 we obtain 61 which is shown as
1900-Mar-01 since that date has serial number 60.

Note that there is no evidence of a "date bug". Also the current
OpenFormula draft does not specify how serial numbers are created, or
more specifically that they may not contain any skips, there is no bug
of any other type in sight!

Note that =datedif(A2,A4,"d") results as expected in 1 since these are
the true date differences!

Andreas
-- 
Andreas J. Guelzow <aguelzow@pyrshep.ca>


---------------------------------------------------------------------
To unsubscribe from this mail list, you must leave the OASIS TC that
generates this mail.  Follow this link to all your TCs in OASIS at:
https://www.oasis-open.org/apps/org/workgroup/portal/my_workgroups.php