Re: [office] ODD{F,L}, Yield and Price spreadsheet functions.

[robert_weir@us.ibm.com]

"Andreas J. Guelzow" <aguelzow@math.concordia.ab.ca>
wrote on 05/15/2008 12:48:07 AM:

> Is there any descriptions somewhere how this is supposed to work in
the
> context of importing files from other common formats?
> 
> Imagine: [.A1]      34556
>          [.A2]      56643
>          [.A3]      =[.A1]
- 3 * [.A2]
>          [.A4]      =DAYS360([.A1];[.A2])

The only interesting thing here is in A4, where there
is an implicit conversion from a number to a date.  The other calculations
are purely numeric.

Excel will do this conversion differently based on
whether the spreadsheet is in 1900-mode or 1904-mode.  This was originally
a Windows versus Mac distinction.

OOXML has a different set of modes:

1900 date base system -- expresses years from -9999
to 99999 with origin at December 30th, 1899 = 0

1900 backward compatibility system -- expresses years
from 1900 to 9999 with January 1st, 1900 = 1 (default system)

1904 backwards compatibility system -- expresses years
from 1904 to 9999 with January 1st, 1904  = 0.

Only the default mode is implemented in Excel 2007.

So realistically, all a convertor needs to worry about
today is the 1900/1904 distinction.

An ODF table (including a spreadsheet) allows a table:null-date
element that specifies the date original, i.e., what date is equivalent
to the number 0.  The default value is 1899-12-30.  So this would
be the same as the 1900 date base system in OOXML.

So in the case of importing an existing Excel document,
the easiest thing for a convertor to do would be to set the table:null-date
value in the ODF document to correspond to the conventions used by the
source Excel document.

The hard case would be when importing content from
multiple source Excel documents into a single ODF document, say one using
1900 conventions, one using 1904 conventions, one using OOXML conventions,
etc.  I don't think this works even if you did it all in Excel.  

Generally, relying on the details of implementation's
numeric-to-date conversions will result in a non-portable document.  

-Rob