OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.

# 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]