DATEVALUE isn't currently supported, I realize, too, so... On Tue, Dec 12, 2017 at 8:59 AM, Greg Woolsey <greg.wool...@gmail.com> wrote:
> This sounds like it should be a Bugzilla issue. The Excel documentation > [1] says "Text can be in any of the constant number, date, or time formats > recognized by Microsoft Excel." I think that means the function needs to be > rewritten in terms of the cell formats defined in > org.apache.poi.ss.usermodel.BuiltinFormats, Checking would need to be > strict, since most of them start with digits, and lenient parsing would > make the result ambiguous. Order would also matter, and require some > testing in Excel to see how it handles formats like "h:mm" and "mm:ss" - > values like "1:11" match both. A quick test of this value shows Excel > converts it with the "h:mm" format, which has a lower built-in index than > "mm:ss". So perhaps just attempting to parse in built-in index order is > sufficient. > > Also present are the fractional data formats - I don't know if POI parses > those yet or not. > > Using Format instances will make this function incredibly slow, as those > are so heavy-weight to construct and use. We can't easily cache them > either, since they are not thread safe. A ThreadLocal Map could be used, I > suppose, and lazy-populated, but even that would only help a little. > > Sounds like, from the same Excel help page, this function should also be > called implicitly whenever a formula needs to use a String value in a > numeric context. > > > [1] > https://urldefense.proofpoint.com/v2/url?u=https-3A__ > support.office.com_en-2Dus_article_VALUE-2Dfunction- > 2D257d0108-2D07dc-2D437d-2Dae1c-2Dbc2d3953d8c2&d=DwIFaQ&c= > dmLomitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN3 > 3stXXb3Psthjw&m=A-h6HE-pwhhmdsnyNXVeQUOADGf5O9XFaA9Ik > HejQZM&s=rax8HzzVk34s6HLibxE-gWtf-oeyghcc66PZ2sPOCLU&e= > > > On Mon, Dec 11, 2017 at 3:34 PM Blake Watson <blake.wat...@pnmac.com> > wrote: > > > In Excel, if I have a cell with any of these: > > > > =VALUE("12-1-2017") > > =VALUE("2017/11/05") > > =VALUE("03/31/2015") > > > > Excel recognizes it's a date and returns the appropriate Double. In POI, > > if I evaluate the cell, I get an error back. Looking at the code (and > > documentation) for Value, it seems as though POI expects a non-date > number. > > There's also DateValue, of course. > > > > I think, Value should evaluate as Excel's VALUE function does. Unless I > > miss something. In any event, VALUE in a cell should be something that > POI > > should be able to catch. > > -- > > > > *Blake Watson* > > > > *PNMAC* > > Application Development Manager > > 5898 Condor Drive > > Moorpark, CA 93021 > > (805) 330.4911 x7742 <(805)%20330-4911> > > blake.wat...@pnmac.com > > www.PennyMacUSA.com <http://www.pennymacusa.com/> > > > -- *Blake Watson* *PNMAC* Application Development Manager 5898 Condor Drive Moorpark, CA 93021 (805) 330.4911 x7742 blake.wat...@pnmac.com www.PennyMacUSA.com <http://www.pennymacusa.com/>
