RE: [office-formula] Conflict between Part 1 and Part 2

["Andreas J. Guelzow" <andreas.guelzow@concordia.ab.ca>]

On Fri, 2010-02-05 at 10:13 -0700, robert_weir@us.ibm.com wrote:
> "Andreas J. Guelzow" <andreas.guelzow@concordia.ab.ca> wrote on 02/05/2010 
> 11:08:43 AM:
> 
> > > 
> > > There are other date-related bugs in spreadsheets.  If I type 0 into 
> Excel 
> > > and format it as a date, it displays "1/0/1900".  Do we need an 
> attribute 
> > > for that as well? 
> > 
> > Gnumeric does _not_ have the  1900-02-29 date, nevertheless it has a
> > skip, ie. the above difference is 2. 
> > 
> > You seem to claim that the only reasonable number to date mapping or
> > date to number mapping is such that (1900-03-01 minus 1900-02-29) is 1.
> > That is simply false. There are many reasonable ones, and specifically a
> > small number currently in use. I object to the characterization of any
> > choice other than one's favourite one as a "bug".
> > 
> 
> Well, to be perfectly honest, my opinion is that having any representation 
> for *1900-02-29 is unreasonable. It is just as much a bug as having a 
> representation for the "41st of Twelvetober". 

I agree. Any representation for *1900-02-29 is unreasonable. There is no
such date.
> 
> However, my expectations for the number of days between 1900-02-28 and 
> 1900-03-01 is purely based on expectations of what would occur if 1900 
> were a leap year, in which case there would be a 1900-02-29. 

I see a distinction between "difference in serial numbers" and "number
of days between". The latter is DAYS(...).  

> 
> That said, I admit I had not considered that some implementations might 
> have two 1900-02-28's in a row.  Thanks for pointing that out.

I don't think I ever pointed anything like that out. There are
applications in which not every integer is a valid date serial number.
And there are applications that behave as if it make sense to permit
large negative numbers as serial numbers corresponding to Gregorian
dates.

> 
> The tricky part is still the behavior.  For example, WEEKDAY() gives the 
> day of the week for a date.  Take 1900-02-27.  Both Excel and Gnumeric map 
> this to serial value 58.  OpenOffice calls it 59.  But Gnumeric and 
> OpenOffice correctly note that it is a Tuesday, even though they differ on 
> the serial number.  Excel incorrectly calls it a Monday, even though it 
> shares the same serial number of Gnumeric.
> 
> So I don't think a skip value clarifies behavior here.

The real world does not have serial numbers. 1900-02-27 is a Tuesday, so
saying it is a Monday is a bug. I am not concerned with bugs.
Saying that the serial number of 1900-02-27 is 59 (or 58) is a choice,
not a bug. We should allow for these choices. 

> 
> > I am usually told that "we can't do that because implementation are
> > doing it differently". As a consequence we seem to include such nonsense
> > functions as CHITEST and provide for duplicate versions of lots of other
> > functions. Either we worry about current implementations or we don't.
> > 
> 
> I think adding an alias for a function is something that is relatively 
> easy for an implementor to do, yes? 

How many different names does it take before it becomes primarily
confusing to users? But you are right it doesn't take any more work than
adding an attribute.
> 
> 
> > As far as the expected behaviour is concerned, a combination of true
> > null-date plus an attribute about a skip would allow evaluators to
> > correctly deal with post 1900-3-1 dates and warn a user about potential
> > problems with pre 1900-3-1 dates. As long as users use number <-> date
> > conversions in their calculations there will always be a portability
> > issue. At least we can provide the tools to have users advised about
> > them.
> > 
> 
> We already have null-date.  skip-date would be defined as what?  "The 
> number of date serial numbers between 1900-02-28 and 1900-03-01"?  By 
> default it would be 0.  Excel could set it to 1.  Gnumeric could set it to 
> 2.  Is that the idea?

Basically, except that Excel and Gnumeric would have the same value
which is 1 larger than OOo.

>  Would there be any other values in use that we know 
> of?  Are there any other reasonable values?  If we can constrain it to 0, 
> 1, or 2, I'd be more comfortable.

I think in fact it is 0 or 1 (or 1 and 2 depending on the exact
definition). So its really a boolean: skip or no-skip. (Of course for
Gnumeric this assumes a null-date prior to 1900-03-01, if you are
running Gnumeric with null-date that is later, we don't have that skip.)

Andreas