RE: [oic] TIME function in ODF vs OOXML

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

I agree with you concerning definitions that could be more precise, especially where simple math is involved.  On the other hand, OpenFormula had a great deal of effort and open scrutiny over a lengthy period to get where it is.   

I opened up Excel 2010 and entered that formula into A1 without any formatting.  It automatically presents as "04:04 AM" in my configuration.  I set B1 to be =A1 and formatted B1 to numeric with 17 decimal places. Voila, 
0.16944444444444500.

My math says TIME(26,120,240) should be the same as TIME(28,4,0) and the same as TIME(4,4,0) so Excel is doing what is reasonably expected.  

If I do C1 = B1*24*60*60 I see 14640. with 17 0s after the decimal, and that agrees with 4*60+4*60*60 seconds exactly.  If I enter B2 = 0.1694444444444560, that value is presented for the cell.  But C2 = B2*24*60*60 is 14640.000000001 !

The example in the OOXML specification doesn't have to be exact out that far, but it is interesting that it isn't.

 - Dennis

-----Original Message-----
From: oic@lists.oasis-open.org [mailto:oic@lists.oasis-open.org] On Behalf Of Andreas J. Guelzow
Sent: Tuesday, May 22, 2012 21:19
To: oic@lists.oasis-open.org
Subject: RE: [oic] TIME function in ODF vs OOXML

On Tue, 2012-05-22 at 21:19 -0600, Dennis E. Hamilton wrote:
> The two paragraphs that use a crude way of describing the [final] hour
> figure being taken mod 24 are in the unchanged text of OOXML.  I took
> those from the ECMA-676, Third Edition, Part 1.  This text is the same
> as in ISO/IEC 29500-1:2011 section 18.17.7.323 TIME without the
> most-recent AMD.  You can obtain either of those on-line without any
> fee.
> 
> It is clear that one needs to logically do the reductions on range
> from right to left (i.e., seconds, then minutes, then hours, where
> hours and minutes can have carries in from the units to the right).
> 
> This example confirms that, I trust:
> 
>   TIME(26,120,240) = 0.1694444444444560
> 

Well, you are probably right in your interpretation of what 18.17.7.323
was supposed to define. I always find it surprising how many of these
definitions are written in such an inprecise way.

If one just reads what is said there than for TIME(26,120,240) one gets
3 hour values and two minute values. Nowhere does it say that they
should be added and then reduced again. (Of course when one doesn't
reduce one might run into trouble with the range.)

Applied to TIME(47,60,0) I basically get 23 hours + 1 hour = 24 hours,
return serial value 1, which is till in the permissible range. But I
suspect that you are right in that that should be 0.

Andreas  

-- 
Andreas J. Guelzow, PhD, FTICA
Concordia University College of Alberta