Recently it got obvious to me accidentally that the TIMEVALUE() function accepts input which is, taken strictly, no valid time string.
=TIMEVALUE("25:65:70") e.g. returns the number 0.0876157407407408 which results in 02:06:10.000 if formatted using the code [HH]:MM:SS.000.
This means that an excess (>=60) in seconds or in minutes is treated with carry to the minutes and the hours respectively, but multiples of 24 h are still simply suppressed for the display.
Regarding my tests this is the case in V 7.0.1 but not in V 6.4.5.
However, I couldn’t find a related note about the change.
Can somebody tell me if the change was intentional, and for what reason it was made in this inconsistent way concerning the excess in hours (>=24)?
It looks intentional, from Documentation/Calc Functions/TIMEVALUE - The Document Foundation Wiki
Returns:
Returns a real number (0 ≤ x < 1) which is the fractional part of the date-time serial number calculated using the given argument. The returned value is formatted as a number by default, but you can apply other formats as needed.
I see the same result in 6.4.7.2 as in 24.2.3.2
Cheers, Al
@Lupp: I definitely can confirm what Alistair tells, and I also see the ~same 0,0876157407407407 using OOo 3.2:
I’m completely lost here.
I remeber for sure that I thoroughly tested, but I didn’t store the “research experiments” to a file, and now I can’t reproduce the results.
As a possible reason for my errors I thought of the usage of “curly double quotes” (a PITA anyway also in this site), but trying today I got Err 501 this way while yesterday the not working examples returned Err 502.
I feel blamed now.
However, I need to decide whether I can still contribute here, or have to surrender to age and eyes getting worse.
For the moment I will continue, and try to enhance my thoroughness concerning checks.
Anyway I will insist on my more fundamental criticism with respect to TIMEVALUE() (including the specification), the relative inconistencies I see concerning DATEVALUE(), the lack of support for a duration format …
A specific version could have some bug. I do the mistakes all the time; just today, I asked in How to integrate the Libre Office PDF converter into my own C# program, what direction of conversion they need, when the info was already there. I don’t see why feel so strong.
I’d guess that’s because Excel does it that way? Gnumeric does the same, which is a strong indicator.
What are you referring to?
Same with =TIME(25;65;70)
Mainly:
DATEVALUE(): A string not being the representation of a valid date in a supported standard format under the given locale is rejected by returning Err 502.
TIMEVALUE() doesn’t care about validity, but hides multiples of 24 h without a warning for the diplay. Even worse: It cuts off the multipkes.
to_erAck_ answering_in_disask107071.ods (57.0 KB)
@Villeroy TIME() correctly regards the carry seconds/minutes and that’s ok, but it also only returns the fractional part of the result, and that’s bad.
In addition there is a TOD format automatically associated with the returned result. TIME() can’t be used as a surrogate for the missing DURATION().
DATE() doesn’t have such flaws
.
Strangely
-
TIMEVALUE() …
- accepts invalid time strings (minutes/seconds > 60),
- handles carryover second-minute-hour
- discards carryover hour-day (>24 hours)
-
VALUE() applied to time formatted input …
- Rejects invalid time strings (minute/second must be in the 0-59 range)
- correctly handles times >24 hours
This is most likely intentional, to have behavior consistent with Excel (as @erAck already suggested). Not the most intuitive, but should be manageable when you know. In most cases we don’t specify 60+ minutes/seconds in a time string (or do we?).
IOW:
It seems that the TIMEVALUE() is meant only for determining time-of-day, not for assessing timespan.
This is only my guess, based on behavior as discussed. I have not found any formal documentation towards this.
To extract specifically the time of day from a timestamp (discarding date info), use TIMEVALUE()
I have seen generated timestamps with ‘60’ in minute or second field. The sporadic occurrence of this may be the reason for allowing “60+”.
To work with arbitrary manually entered time strings, use VALUE().
A human will normally not specify 60+ minutes/seconds in manual input.