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


Help: OASIS Mailing Lists Help | MarkMail Help

office message

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

Subject: Re: [office] YEARFRAC information

Hi David,

Thanks for tracking these down.  I know this has been a lingering task for OpenFormula for some time now.  Although were were hoping that the OOXML text would provide clarification in this area, not additional ambiguity, it looks like your brute force approach has revealed how Excel actually defines these functions.

My understanding is that JTC1/SC34 does not have a mechanism in place yet for accepting defect reports on OOXML.  But they are working on such a mechanism, and when it is place we can ask Patrick to submit a defect report on behalf of the ODF TC on this point.

I agree with you that these inconsistencies will likely be resolved by changing OOXML to match Excel's behavior.  Since we require resolution now, and can not wait for OOXML maintenance activities, we are probably best off matching what Excel does based on the formulas you derived.

But I'm wondering if we can take this even one level further?  If you compare your formulas with the documented financial authorities' definitions of day count conventions, such as those listed on Wikipedia:  http://en.wikipedia.org/wiki/Day_count_convention, do any of them match exactly?

In other words, if we can say that Basis 0 matched NASD rule 27 as stated in this or that official document, and this matches what Excel does perfectly, then this might be more helpful than merely giving an arbitrary formula seemingly pulled out of the thin air.


Rob Weir
Software Architect
Workplace, Portal and Collaboration Software
IBM Software Group

email: robert_weir@us.ibm.com
phone: 1-978-399-7122

"David A. Wheeler" <dwheeler@dwheeler.com> wrote on 05/19/2008 08:43:51 AM:

> I've published detailed information on YEARFRAC, including
> a detailed definition that matches what Excel _actually_ does
> instead of what OOXML says (they are incompatible):
> We now need to decide what to do with OpenFormula; this affects
> 26 functions.  The 'obvious' answer is to use the definitions that
> are actually compatible with Excel, since there are far more Excel
> .docx spreadsheets than OOXML spreadsheets
> (to my knowledge, there are no OOXML spreadsheet documents).
> If OOXML decides to stay with their incompatible basis
> definitions, we could assign new basis values for
> those algorithms, so we could handle both OOXML and .docx.
> We could assign basis values 16..31 as meaning "the same as
> OOXML basis 0..15"; I think we can appeal to the definition of an
> external specification if it's a standard :-).  I suggest these
> be optional.
> --- David A. Wheeler
> ---------------------------------------------------------------------
> To unsubscribe from this mail list, you must leave the OASIS TC that
> generates this mail.  You may a link to this group and all your TCs in OASIS
> at:

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