RE: [oic] TIME function in ODF vs OOXML

["Dennis E. Hamilton" <dennis.hamilton@acm.org>]

It seems that an advisory is worthwhile, as well as a set of test cases that expose any differences among implementations.

 - Dennis

MUSINGS

I think what clouds this issue is that ODF and ISO 8603 have a time-interval data type for elapsed times, and there may be interaction with that fact in OpenFormula as well.  It is also the case that one can produce time intervals (as a format control) in OOXML and intervals greater than one day, greater than 24 hours (in units of hours rather than days and hours) can be presented.  I accumulate consulting-time billable hours that way [;<).

I don't think that defining Time as a subtype of number is much help, although the definition suggests that TIME(24;0;0) should be 1.0 and not 0 [;<).  

There is no ambiguity in the Semantics in 6.10.17 and that is where the problem comes up.  

I agree that one could make a statement about implementation-defined (not merely -dependent) cases when the parameter values to TIME are outside of the obvious range (i.e., non-negative hh greater than 23, mm greater than 59, etc.)  

And there is an issue of correspondence with ISO 8603 notions of what is acceptable in those notations.

 - Dennis

-----Original Message-----
From: oic@lists.oasis-open.org [mailto:oic@lists.oasis-open.org] On Behalf Of John Haug
Sent: Tuesday, May 22, 2012 11:51
To: oic@lists.oasis-open.org
Subject: RE: [oic] TIME function in ODF vs OOXML 

[Bart]> So if I read this correctly, TIME(48;0;0) in ODF has a serial value of 2, while in OOXML the value is 0

True for OOXML, and Excel does return 0 for TIME(48,0,0).  I’ve attached the section that defines the TIME function.  The AMD didn’t affect TIME.

 

However, in reading what ODF says about TIME, I’m not convinced what the right answer is – it’s underspecified.

 

The relevant parts of 6.10.17 TIME are (ODF 1.2):

Summary: Constructs a time value from hours, minutes, and seconds.

Returns: Time

Semantics: Returns the fraction of the day consumed by the given time, i.e.: ((hours*60*60)+(minutes*60)+seconds)/(24*60*60)

Time is a subtype of number, where a time value of 1 = 1 day = 24 hours.

 

“Returns the fraction of the day” seems to imply the number should be a fractional value of 1.  The definition of “Time” doesn’t help any.  The entirety of 4.3.2 Time is:

Time is a subtype of Number.

Time is represented as a fraction of a day.

 

My understanding is that the intent of the formula subcommittee was to create something compatible with in-market implementations as much as possible, so I’d think that if this had been found before 1.2 was completed it probably would have defined specific results for values between 0 and 24 hours and then left values larger than 24 hours implementation dependent.  Hopefully with some comment about some implementations using a modulo and others simply dividing.

 

That seems the best thing to do, to me.  Interop advisory, bug fix in ODF?

 

John

 

-----Original Message-----
From: oic@lists.oasis-open.org [mailto:oic@lists.oasis-open.org] On Behalf Of Dennis E. Hamilton
Sent: Tuesday, May 22, 2012 10:56 AM
To: oic@lists.oasis-open.org
Subject: RE: [oic] TIME function in ODF vs OOXML 

 

That may be old.  There is an ISO/IEC AMD that does a lot about dates and time and the transition versus "pure" case.  I've seen it but I don't recall whether TIME is touched.  I do know that examples have been touched and that might apply here.

 

-----Original Message-----

From: oic@lists.oasis-open.org <mailto:oic@lists.oasis-open.org>  [mailto:oic@lists.oasis-open.org <mailto:oic@lists.oasis-open.org> ] On Behalf Of Hanssens Bart

Sent: Tuesday, May 22, 2012 01:25

To: oic@lists.oasis-open.org <mailto:oic@lists.oasis-open.org> 

Subject: [oic] TIME function in ODF vs OOXML 

 

Hi,

 

Interesting, but nasty issue for implementers

 

* ODF 1.2, Part 2

6.10.17 TIME: ((hours*60*60) + (minutes*60) + seconds) / (24*60*60)

 

* ECMA 376, 3rd Edition, Part 1

18.7.7.323 TIME: TIME(26,120,240) results in a serial value of 0.169444444...

 

 

So if I read this correctly, TIME(48;0;0) in ODF has a serial value of 2, while in OOXML the value is 0

 

 

Best regards

 

Bart