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/04/2010 
03:48:53 PM:


> > If we had the 1900-leap year flag, how would implementations use it?
> 
> How would applications use the null-date?
> 
> If an application encountered a file created ona program with different
> settings, at least it could warn the user that time calculations may be
> affected.
> 

There are two kinds of dates in a spreadsheet.  There are those that will 
actually be stored as an ISO 8601 string.  The null-date would not be 
needed for those.  We can simply define the behavior of basic date 
calculations:

Date + Number
Date - Number
Date - Date
INT(Date)
Date-INT(Date)

I think we those we can clarify almost all reasonable date calculations 
without needing to say a single thing about the null-date. 

And then there are dates that are created at runtime via the implicit 
number-->date conversion operations.  So for calculating things like the 
value of SIN(DATE(1920,1,1)), then we do need to use the null-date.  This 
would be used for the implicit number-->date conversion, in pseudo-C++ 
terms as:

Date date(Number num) { return null-date + num; }

Where null-date + num is a calendar operation, defined by Gregorian 
calculations, not a numerical calculation.

.
.
.

> 
> Perhaps you could elaborate on how you determined how many applications
> have a skip and how many do not. I could easily claim that more existing
> spreadsheet files have dates stored as serial numbers with the skip than
> there are those that do not have the skip.
> 

Fair question.  I think the easiest way is to test it like this:

Enter the date constant 2/28/1900 into cell A1
Then enter the formula =A1+1 into another cell

If it evaluates to 2/29/1900 then it has the bug.  If it evaluates to 
3/1/1900 then it goes Gregorian calculations correctly, at least in that 
case.

I've tested this, and of the implementations represented on the TC:

Gnumeric, OpenOffice, Symphony, Google and KSpread do it correctly.

Excel does not.

I don't think number of existing documents in non-ODF formats is extremely 
relevant.


-Rob