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, etc.


Rob,

robert_weir@us.ibm.com wrote:
>
> You can go back to the original thread to see how these functions are 
> broken.  I mentioned two things:
>
> 1) The requirements for the Actual/Actual basis for YEARFRAC are 
> defined in two different, contradictory ways.  In one place is says 
> "the year length used is the average length of the years that the 
> range crosses, regardless of where start-date and end-date fall in 
> their respective years" while in another place it says "If the date 
> range includes the date 29 February, the year is 366 days; otherwise 
> it is 365 days."
>
This is based on the resolutions at the BRM. Yes? My understanding was 
that the last resolution passed controls. I will have to look at those 
specifically.
> 2) The definition for the European 30/360 function doesn't make sense. 
>  It is grammatical, but still gibberish.  The text is "For months with 
> 31 days, all dates with a day value of 31 are changed to day 30, 
> including 28 and 29 February."
>
Well, inartfully worded, I suspect: "All months have 30 days." covers 
all the cases. Yes? Looks like someone got tied up in how many days each 
month has. Really irrelevant if you want to say that all months have 30 
days. Just say that, full stop. How an application treats a particular 
date is determined by that rule, every month has 30 days. What more need 
be said?

If it is Feb 28, then we have Feb. 30. If it is Feb 29, then it is Feb 
30. If it is May 31, it is May 30. If it is June 30, well, it is June 
30. What is unclear about that?

But note that all you have to do is write the rule the simplest way 
possible. It is when people try to be clever that stuff goes wrong.

*NOTE* I don't know if that is "correct" or not but will check.
> So without mentioning Excel or another other application, these are 
> obvious problems that prevent us from achieving interoperable results 
> with an implementation of OOXML.
>
> I can write up the specifics for a defect report.
>
> Eike also posted a note on how the function as specified does not 
> match Excel's behavior.
>
I will take a look at your earlier post and Eike's.

Hope you are having a great day!

Patrick
> -Rob
>
>
>
>
> *Patrick Durusau <patrick@durusau.net>*
>
> 04/14/2008 05:55 PM
>
> 	
> To
> 	robert_weir@us.ibm.com
> cc
> 	"Andreas J. Guelzow" <aguelzow@math.concordia.ab.ca>, 
> office@lists.oasis-open.org
> Subject
> 	Re: [office] YEARFRAC, etc.
>
>
>
> 	
>
>
>
>
>
> Rob,
>
> robert_weir@us.ibm.com wrote:
> >
> > Patrick Durusau <patrick@durusau.net> wrote on 04/14/2008 04:53:55 PM:
> >
> > > Rob,
> > >
> > > OK but if I understand David Wheeler's point, it is the case that no
> > one
> > > has reverse engineered the proper definition for YEARFRAC.
> > >
> > > So, having "spotted the problem," have you suggested a solution?
> > >
> >
> > Microsoft should document what Excel does when it calculates YEARFRAC
> > and it should do it completely and unambiguously.
> >
> Yes, and you knew when you wrote that line that I completely agree. But,
> the situation is that it didn't but noting that doesn't get us any
> closer to a solution. Which is what I want, a solution.
> > > Yes, a lot of other people have failed to come up with the 
> solution, so
> > > how does that observation help? None of those reporting the problem
> > have
> > > offered a solution either.
> > >
> >
> > See above.  The solution is for OOXML to document Excel's behavior.
> > That is the purpose of OOXML after all, as stated in their scope
> > clause.  Since Excel is a proprietary, closed-source vendor product,
> > no one but Microsoft can peer inside to answer this question.
> >
> > In any case, by raising the issue to your attention I'm hoping maybe
> > that you can use some of your contacts to get an answer we can use.
> >  You are our SC34 liaison, right?  I'd rather not wait until October
> > for SC34 to figure out how they want to do maintenance.  Perhaps you
> > can enter a defect report to SC34 on this question, give Murata-san's
> > new ad-hoc something concrete to work with.  If it will help, I can
> > draft the defect report, we can approve it at a future TC call, and
> > you can get it off to SC34 as soon as the official final text of OOXML
> > is released.
> >
> I was unaware that YEARFRAC was still broken. I have sent out requests
> through non-official channels pointing this out and the absolute need
> for a fix. But yes, let's also file a defect report about this and any
> other formula issues as soon as the list goes up. Actually I will pursue
> them separately as well.
>
> Now, aside from simply saying that YEARFRAC is still broken, can someone
> rather plainly tell me in what way it is still broken? As compared to
> what? I assume that the current definition does not provide a definition
> that is compatible with all the variations on YEARFRAC over the years.
>
> If that is the question, then we need to ask it in that way. That is,
> define YEARFRAC for Excel 97, Excel 2000, Excel 2003, etc. (I skipped
> the service packs). Otherwise, if someone were to ask me to define
> YEARFRAC, I would come up with the definition that I want to use going
> forward and simply ignore all the legacy stuff. Yes, spreadsheets would
> break.
>
> However, even more spreadsheets will break if you preserve the various
> definitions of YEARFRAC as not every application will implement all of
> them and some will fail gracefully and others will not. It really is a
> legacy support issue.
>
> Does that help?
>
> Hope you are having a great day!
>
> Patrick
>
> PS: And yes, while there are no definite structures in place, I am
> confident that the WG that will be formed to maintain OpenXML will be
> aggressive about its maintenance responsibilities. I realize that most
> of the members of the TC don't know members of SC 34 but I have spent
> almost a decade working with them and have every confidence in their
> abilities. For example, MURATA Makoto, the anticipated convener of WG 4,
> originated with Jame Clark RELAX-NG.  Several of the architects of the
> markup paradigm are still active members of SC 34 so I think it should
> be given an opportunity to do its work.
>
> > > Here is how to move off dead center:
> > >
> > > 1) Agree that YEARFRAC has not been fully/properly/etc. defined
> > > 2) Agree that both ODF and OOXML need to have the same "proper"
> > > definition of YEARFRAC
> > > 3) Either derive one ourselves or in concert with others who are
> > > concerned about the same issue (others being a reference to OOXML)
> > > 4) See that both we and anyone else with a formula standard uses the
> > > result of #3.
> > >
> > > Granted that doesn't provide many opportunities for clever remarks but
> > > none of that is going to be found in ODF 1.2.
> > >
> > > Hopefully a good definition of YEARFRAC will be.
> > >
> > > Hope you are having a great day!
> > >
> > > Patrick
> > >
> > > robert_weir@us.ibm.com wrote:
> > > >
> > > > Keep in mind the history of these date basis definitions in OOXML.
> > > >
> > > > I first reported the problem on July 9th, 2007.  This became part of
> > > > the comments the US submitted along with our ballot response in 
> Sept.
> > > >
> > > > Ecma, in their response to the ballot comments failed to fix the
> > > > problem.  They botched the definitions.
> > > >
> > > > The DIS 29500 BRM in Geneva, failed to fix the definitions.  For 
> lack
> > > > of time the BRM decided to accept Ecma's defective text changes.
> > > >
> > > > So what we end up with now, went 100% through the Ecma, JTC1 and 
> SC34
> > > > processes.  No one along the way has managed to correct these 
> errors.
> > > >
> > > > These errors have already been reported, botched and approved any
> > > > ways.  I'm not sure repetition of the same process by the same 
> people
> > > > is necessarily going to lead to great improvements.
> > > >
> > > > In any case, from the ODF 1.2 perspective, I think we have no choice
> > > > but to ignore OOXML's definitions and try to reverse engineer
> > > > Microsoft Excel.  It could be a good marketing statement -- 
> OOXML may
> > > > be better at calculating wrong leap years and incorrect footnote
> > > > placement like Word 95 did, but only ODF defines financial
> > spreadsheet
> > > > functions in a way which is compatible with current and legacy
> > > > versions of Microsoft Excel.
> > > >
> > > >
> > > > -Rob
> > > >
> > >
> > > --
> > > Patrick Durusau
> > > patrick@durusau.net
> > > Chair, V1 - US TAG to JTC 1/SC 34
> > > Convener, JTC 1/SC 34/WG 3 (Topic Maps)
> > > Editor, OpenDocument Format TC (OASIS), Project Editor ISO/IEC 26300
> > > Co-Editor, ISO/IEC 13250-1, 13250-5 (Topic Maps)
> > >
>
> -- 
> Patrick Durusau
> patrick@durusau.net
> Chair, V1 - US TAG to JTC 1/SC 34
> Convener, JTC 1/SC 34/WG 3 (Topic Maps)
> Editor, OpenDocument Format TC (OASIS), Project Editor ISO/IEC 26300
> Co-Editor, ISO/IEC 13250-1, 13250-5 (Topic Maps)
>
>

-- 
Patrick Durusau
patrick@durusau.net
Chair, V1 - US TAG to JTC 1/SC 34
Convener, JTC 1/SC 34/WG 3 (Topic Maps)
Editor, OpenDocument Format TC (OASIS), Project Editor ISO/IEC 26300
Co-Editor, ISO/IEC 13250-1, 13250-5 (Topic Maps)



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