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

[robert_weir@us.ibm.com]

"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". 

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. 

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. 

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.

> 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? 


> 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? 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.

-Rob

> Andreas
> 
> > 
> > -Rob
> > 
> > 
> > > As far as I am concerned having the standard suggest that one 
falsifies
> > > the null-date when saving is unacceptable. 
> > > 
> > > If for whatever reason people object to discoverability and want the
> > > difference in serial numbers for dates prior to 1900/3/1, then the
> > > null-date ought to be defined differently, by perhaps giving the 
date
> > > corresponding to serial=100.
> > > 
> > > Andreas
> > > 
> > > 
> > > -- 
> > > Andreas J. Guelzow <andreas.guelzow@concordia.ab.ca>
> > > Concordia University College of Alberta
> > > 
> > > 
> > > 
---------------------------------------------------------------------
> > > 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 
> > > 
> > 
> > 
> > ---------------------------------------------------------------------
> > 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 

> > 
> 
>