GNOME Bugzilla – Bug 676596
TIME incompatibility
Last modified: 2012-05-23 06:07:13 UTC
The definition of the ODF TIME function is: Summary: Constructs a time value from hours, minutes, and seconds. Syntax: TIME( Number hours ; Number minutes ; Number seconds ) Returns: Time Constraints: None. Evaluators may first perform INT() on the hour, minute, and second before doing the calculation. 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. Hours, minutes, and seconds may be any number (they shall not be limited to the ranges 0..24, 0..59, or 0..60 respectively). Excel on the other hand, returns: ((hours*60*60)+(minutes*60)+seconds)/(24*60*60) - TRUNC (((hours*60*60)+(minutes*60)+seconds)/(24*60*60)) which is always a value between 0 and 1 (possibly 0 but never 1) Gnumeric's TIME function is currently compatible with the ODF version. (Note that Apache OpenOffice uses Excel's definition. It is claimed that LibreOffice 3.5.x also uses Excel's although LO 3.4 used the ODF definition.)
What do they mean by "Evaluators may first perform INT() on the hour, minute, and second before doing the calculation."? That the implementation may ignore any fractional part of the argument? We should be XL compatible for functions. If it matters, we can have an ODF.TIME too (or whatever it is we name such functions).
"first performing INT" means rounding towards negative infinity, ie. for positive numbers this means truncating. I fully agree that we should be Excel compatible here (that's why I filed this as a bug). Before we introduce an ODF.TIME function we really have to see what hapens in current OASIS OIC/ODF discussions. See also http://ajg.pyrshep.ca/archives/111
This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report.