Re: [office-formula] 6.9.17 TIMEVALUE

[Eike Rathke <erack@sun.com>]

Hi Eric,

On Monday, 2009-03-09 17:43:14 +0000, Eric Patterson wrote:

> I've been looking at the definition of the TIMEVALUE function, and one of the test cases appears to conflict with the text.
> 
> Given the following text:
> 
> "If the text of T has a combined date and time format, e.g. YYYY-MM-DD
> HH:MM:SS, the fractional part of the date serial number is returned.
> If the text of T does not have a time format, an implementation may
> attempt to convert the number another way (e.g., using VALUE), or it
> may return an error (this is implementation-dependent). "
> 
> and the last test case:
> 
>         =TIMEVALUE("1999-11-22 06:05:07")=TIME(6;5;7)    (Result=True)
> 
> 
> The first sentence indicates that when the string contains a combined
> date and time, that the time should be returned which seems to match
> with the test case.  The second sentence however seems to indicate
> that an implementation can attempt to convert it in a different way,
> which I would interpret as returning the date and time (whole and
> fractional part of the number) or an error.  Was this the intent?

If I understood correctly, confusion arises from "text has a combined
date and time format" versus "text does not have a time format". Maybe
things are clearer if we changed "If the text of T does not have a time
format" to "If the text of T does not contain a time format" instead?

> For reference, both Excel and Calc currently fail the above test case.

Calc versions <3.1 always included the date portion's integer part,
similar for DATEVALUE included the fractional part, which changed for
version 3.1

Excel fails because the test case compares floating point values, due to
accuracy in representation these should always be rounded before
comparing for equalness; I suggest to change the test case to

=ROUND(TIMEVALUE("1999-11-22 06:05:07");10)=ROUND(TIME(6;5;7);10)

Other opinions?

  Eike

-- 
Automatic string conversions considered dangerous. They are the GOTO statements
of spreadsheets.  --Robert Weir on the OpenDocument formula subcommittee's list.

PGP signature