# office message

[Date Prev]
| [Thread Prev]
| [Thread Next]
| [Date Next]
--
[Date Index]
| [Thread Index]
| [List Home]

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

*From*: **robert_weir@us.ibm.com**
*To*: "Andreas J. Guelzow" <aguelzow@math.concordia.ab.ca>
*Date*: Thu, 15 May 2008 09:45:27 -0400

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

[Date Prev]
| [Thread Prev]
| [Thread Next]
| [Date Next]
--
[Date Index]
| [Thread Index]
| [List Home]