Designed Incompatibility

In certain circles, Microsoft is usually blamed for "extend, embrace, extinguish" where the "embrace" stands for purposefully changing a specification to create incompatibilities. This post is not addressing Microsoft but another group that is usually more standards compliant: the Apache Foundation.

In the release notes of the new Apache OpenOffice release, changes to the TIME function are advertised:

Spreadsheet: TIME function returns values 00:00:00 <= x <= 23:59:59.999
The TIME spreadsheet function returns values between 00:00:00 and 23:59:59.999... inclusive and wraps values equal to or greater than 24:00:00.

It is easy to verify that Apache OpenOffice saves this function as the ODF OpenFormula function TIME:

<table:table-cell table:formula="of:=TIME(48;0;0)" office:value-type="time" office:time-value="PT00H00M00S"><text:p>00:00:00</text:p></table:table-cell>

This means that normally this function should be compatible with the ODF 1.2 part 2 specification as outlined in 6.10.17, where it says:

Syntax: TIME( Number hours ; Number minutes ; Number seconds )
Semantics: Returns the fraction of the day consumed by the given time, i.e.: ((hours*60*60)+(minutes*60)+seconds)/(24*60*60)
Hours, minutes, and seconds may be any number (they shall not be limited to the ranges 0..24,0..59, or 0..60 respectively).

So ODF requires TIME(48;0;0) to evaluate to 2, while in the new Apache OpenOffice it evaluates to 0.

Note that this is the value calculated, depending on the cell format this value could look differently.  Both LibreOffice and Gnumeric evaluate TIME(48;0;0) as 2, but LibreOffice shows it as 48:0:0 while Gnumeric shows it as 00:00.