can reproduce on AOO3.4 release version(r1327774)

The function throws Err:502 when the resulting number is smaller than zero:
=TIME(0;0;-1)

There is no such problem with the DATE function.

AOO behaves exactly like Excel here: negatives times are invalid, and anything over 24 day wraps around. So ODFF and Excel are incompatible when it comes to the time function. What to do?

(In reply to damjan from comment #3)
> AOO behaves exactly like Excel here: negatives times are invalid, and
> anything over 24 day wraps around. So ODFF and Excel are incompatible when
> it comes to the time function. What to do?

I meant anything over 24 hours = 1 day wraps around.

(In reply to damjan from comment #4)
> (In reply to damjan from comment #3)
> > AOO behaves exactly like Excel here: negatives times are invalid, and
> > anything over 24 day wraps around. So ODFF and Excel are incompatible when
> > it comes to the time function. What to do?
> 
> I meant anything over 24 hours = 1 day wraps around.


I think the problem is with the term "fraction of the day" in the OpenFormula specification, which means the value would be non-negative and less than (or maybenot more than) 1.0.

Let's look closer into this.  The issue is pretty old and we might need to see what has happened with ODF in the meantime as well.

My vote is to do this the Excel 2013 way (and the LibreOffice 5 way for negative results).  One could argue that Andreas misunderstands the ODF 1.2 specification, but it doesn't matter.

 - - - - - - - - - - - -

I remember what this is about.

For ODF 1.2 Part 2 OpenFormula, note the definitions in 4.3.2 Time

  "Time is a subtype of Number
  "Time is represented as a fraction of a day"

and 4.3.3 Date,

  "Date is a subtype of Number.
  "Date is represented by an integer value."
  "A serial date is the expression of a date as the number of days elapsed from
a start date called the epoch."

  "Evaluators SHALL support all dates from 1904-01-01 through 9999-12-31 (inclusive) in calculations, SHOULD support dates from 1899-12-30 through 9999-12-31 (inclusive) and MAY support a wider range.

There are important notes about not assuming a particular epoch value, dealing with whether or not 1900 is a leap year or not, and that negative values MAY be supported to deal with dates before the epoch.

and 4.3.4 DateTime

"DateTime is a subtype of Number.  It is a Date plus Time."

IMPORTANT: For the above to work as a calculation, rather than how to understand the number, Time must not be negative.  That would end up being in the day before the Date-expressed date.

So, TIME returning a value only in the range 0 <= time < 1 is admissible.

There may be bugs, and it may be a function of weird parameter cases, but the specification in 6.10.17 states that the result is a Time value (see 4.3.2).

HERE'S THE PROBLEM

The OpenFormula definition is MEANT to be compatible with Excel's definition, at least for the ranges that Excel accepts.

Note that 6.10.17 is expected to produce a Time value, not a DateTime value, and that negative DateTime values are not required to be supported.

Under that interpretation it is a mistake to assume that 60*(hours*60+minutes)+seconds is the answer (that would likely be a DateTime), it is the Time portion (the fraction of the day) consumed by the result.  Treatment of negative results from that formula is not covered by the ODF 1.2 specification at all.

ABOUT EXCEL COMPATIBILITY

However, Excel does indeed deliver a DayTime result, just as Andreas asserts.  Excel does not allow negative DayTime values, and allowing them in Calc will lead to different results.

Furthermore, our cousins at LibreOffice have "corrected" Calc to satisfy the sample test file.

If it were up to me, I would say interoperability in reality is more important than strict reading of the specification.

I haven't checked to see if there are any corrections to be made to TIME() in ODF 1.2 (or ODF 1.3).  Although there are sound principles on TIME() producing a clean Time value, that is not the reality and interoperability in practice is far more important (I say).

What is the Excel way and the LibreOffice way?

AOO do it right in my opinion.

"Semantics: Returns the fraction of the day consumed by the given time"
It simply returns the clock time at the resulting day.

So any result will and must be in the range 0 (=00:00:00) <= result < 1 (=24:00:00).
TIME(24;0;0) = 00:00:00
TIME(0;120;0) = 02:00:00
TIME(-1;120;0) = 01:00:00 (one hour before 120 minutes)

If we allow all negative parameters then there must be
TIME(-1:0;0) = 23:00:00 (one hour before 00:00:00)

This is exactly the same what TIMEVALUE("-1:0:0") returns.

TIME and TIMEVALUE should always give the same result as they do for all positive results at this time:
TIMEVALUE("24:0:0") = 00:00:00
TIMEVALUE("0:120:0") = 02:00:00
TIMEVALUE("-1:120:0") = 01:00:00 (one hour before 120 minutes)

My vote: 0 <= TIME(a;b;c) = TIMEVALUE("a:b:c") < 1

FYI: Recent discussion on the user forum about the meaning of negative time values which do make sense:
https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=78985

Excel is not the reference when dealing with date/times, particularly not when negative values are involved which Excel can not handle at all.

I don't see any reason why function TIME should not behave like function DATE which calculates correct positive and negative day numbers for any positive or negative integers you throw at it. Ok, the year has to be Gregorian (>1582).

The cell value calculated by TIME should be as simple as 
=hours/24+minutes/1440+seconds/86400 => positive or negative number representing the amount of days.
This value can be formatted as a point of time HH:MM:SS or as a period of time [HH]:MM:SS

Current implementation of TIME works like this:
=MOD(hours/24+minutes/1440+seconds/86400;1) but only if the result is a positive value which is a needless restriction since contrary to Excel we do support negative numbers formatted as date/times and the DATE function calculates all days between the years 1582 and 1899 very well, even with negative and zero months and days.

=DATE(1900;0;0) => 1899-11-30 zeroth month is December, zeroth day of December is 30 November
=DATE(1700;-1;-1) => 1699-10-30 same logic with 2 months and 2 days before 1st of January 1700

> Excel is not the reference when dealing with date/times, particularly not
> when negative values are involved which Excel can not handle at all.

That's right! (But not only in this case.)

> I don't see any reason why function TIME should not behave like function
> DATE which calculates correct positive and negative day numbers for any
> positive or negative integers you throw at it. Ok, the year has to be
> Gregorian (>1582).

The difference is:
A calendar date is any date >= 1.1.1
So the question is: what is the/a date before 1.1.1?
(Is the year before common era [B.C.E.] the year 0 or -1?)
We can define the day 0 to 31.12.0. But if we count from there there is no negative day.
But Calc defines the representation of the days/calendar dates with an integer with 0 = 30.12.1899

The minimal value is =DATE(1582;10;15) = -115858
The internal _representation_ of a (positive!) day can by a negative integer.

A (clock) time has no date count and is always a value 00:00:00 (0) <= time < 24:00:00 (1).


> The cell value calculated by TIME should be as simple as 
> =hours/24+minutes/1440+seconds/86400 => positive or negative number
> representing the amount of days.
> This value can be formatted as a point of time HH:MM:SS or as a period of
> time [HH]:MM:SS

And here comes the difficulty:

TIME() calculates a (clock) time not a period of time! And this is 100 % equivalent to DATE().

In your opinion there should be
=TIME(-6;0;0) = -0.25 = 18:00:00 (HH:MM:SS) =(?) -06:00:00 ([HH]:MM:SS)
=TIME(-30;0;0) = -1.25 = 18:00:00 (HH:MM:SS) =(?) -30:00:00 ([HH]:MM:SS)

I vote for
=TIME(-6;0;0) = 0.75 = 18:00:00 (HH:MM:SS) =(!) 18:00:00 ([HH]:MM:SS)
=TIME(-30;0;0) = 0.75 = 18:00:00 (HH:MM:SS) =(!) 18:00:00 ([HH]:MM:SS)
in compatibility to the actual (and for me right) implementation.

(In reply to Andreas Säger from comment #8)
> FYI: Recent discussion on the user forum about the meaning of negative time
> values which do make sense:
> https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=78985

I don't believe there is any concern, especially with respect to OpenFormula, for cases that Excel does not handle.  However, the result for cases that Excel does handle (all but the six negative results in the "sample test file") should agree.

That is minimum interoperability.  ODF does not require implementation of negative DayTime values, and the sample test file recognizes that.  

LibreOffice also matches Excel where Excel provides results, and uses an extended DayTime range otherwise.  It seems ultimately pragmatic that AOO do precisely the same thing as LibreOffice, providing interoperability among all of the major ODF-supporting implementations and any software using the historical result of Excel.

It is interesting that the handy identity T = INT(T) + TIME(HOURS(T),MINUTES(T),SECONDS(T)) is preserved (isn't it?) whatever the approach, since months and years are not considered, a good thing for many reasons, and the HOURS, MINUTES, and SECONDS functions provide non-negative results.

PS: The sample test file fails to obtain an usable result in Excel only where the formula produces a negative result.  Where negative parameter values are tested, and the result is non-negative, Excel provides the same result as the formula. 

PPS: The OpenFormula specification explicitly allows the TIME implementation to use the INT of each of its parameters.

PPPS: It would be useful to have interoperability notes that identify what choices are made where the ODF OpenFormula specification is permissive.