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

[Patrick Durusau <patrick@durusau.net>]

Andreas,

Let me see if I can summarize what is being suggested:

1) Add a table:skip-date attribute to the <table:calculation-settings> 
element in part 1.

2) Values for the table:skip-date attribute are: 0 and 1. Defaults to 0. 
With the following semantics:

0 - In the construction of serial dates, if the epoch starts prior to 
1900-03-01, the number of days between 1900-02-28 and 1900-03-01 is zero.

1 - In the construction of serial dates, if the epoch starts prior to 
1900-03-01, the number of days between 1900-02-28 and 1900-03-01 is one.

3) No change required in the current table:date-value attribute of the 
<table:null-date> element, which currently defaults to 12/30/1899.

4) To be consistent with part 1, the "shall" requirement for support in 
part 2 should be amended to say dates from 1899-12-31 through 9999-12-31 
shall be supported.

Does that met the requirement that applications be able to signal their 
treatment of the date in question?

Particularly when the dates appear with no outward sign concerning the 
presumptions under which they were generated. (Eric's point from earlier 
in the thread.)

Hope everyone is having a great weekend!

Patrick

Andreas J. Guelzow wrote:
> 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
>  
>
>
>
> ---------------------------------------------------------------------
> 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 
>
>
>   

-- 
Patrick Durusau
patrick@durusau.net
Chair, V1 - US TAG to JTC 1/SC 34
Convener, JTC 1/SC 34/WG 3 (Topic Maps)
Editor, OpenDocument Format TC (OASIS), Project Editor ISO/IEC 26300
Co-Editor, ISO/IEC 13250-1, 13250-5 (Topic Maps)