You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2019/03/16 15:43:43 UTC
[Bug 61472] Unable to evaluate dependent cell formula
https://bz.apache.org/bugzilla/show_bug.cgi?id=61472
Yegor Kozlov <ye...@dinom.ru> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|--- |FIXED
Status|NEW |RESOLVED
--- Comment #7 from Yegor Kozlov <ye...@dinom.ru> ---
Fixed in r1855662
The root cause was that OperandResolver#coerceValueToDouble didn't recognize
date/time strings and failed for inputs like "12:24" or "2019/01/13".
If a string represents date or time Excel converts it to number automatically.
The following examples are valid MS Excel formulas:
=TEXT(NOW(),"hh:mm:ss") + 0
=TEXT(NOW(),"H:mm AM/PM") + 0
="2005/01/01" + 0
="2018/01/01 12:33:54" + 0
Date strings are evaluated as a number of days since January 1, 1900, for
example,
="1900/01/01" + 0 evaluates to 1
="1900/01/02" + 0 evaluates to 2
="2019/01/19" + 0 evaluates to 43483
Time is evaluated as a decimal number ranging from 0 to 0.99988426,
representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).
="00:00" + 0 evaluates to 0.0
="12:00" + 0 evaluates to 0.5
="23:59:59" + 0 evaluates to 0.99988426
Date and time can be used in combination, e.g.
="2019/1/18 3:43:00 PM"+0 evaluates to 43483.65486
The valid separator for date is '/' (slash) and for time is ':' (colon). Any
other separators such as '-' result in #VALUE!, for example,
="1900-01-01" + 0 evaluates to #VALUE!
This fix also applies to the VALUE function which converts a text string that
represents a number to a number .
="2019/1/18 3:43:00 PM"+0
is equivalent to
=VALUE("2019/1/18 3:43:00 PM")+0
You can use implicit conversion of VALUE, the result will be the same.
Regards,
Yegor
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org