And documentation error: parseYYYYMMDDDate doesn't return a double per the text, but a java.util.Date.
parseYYYYMMDDDate public static java.util.Date parseYYYYMMDDDate(java.lang.String dateStr) Converts a string of format "YYYY/MM/DD" to its (Excel) numeric equivalent Returns:a double representing the (integer) number of days since the start of the Excel epoch On Tue, Dec 12, 2017 at 1:56 PM, Blake Watson <[email protected]> wrote: > DATEVALUE isn't currently supported, I realize, too, so... > > On Tue, Dec 12, 2017 at 8:59 AM, Greg Woolsey <[email protected]> > 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=dmLo >> mitc30UP5j2qU8E1rg&r=p42pHJHEwFZOHtVFHKJUdL2fYbroN33stXXb3Ps >> thjw&m=A-h6HE-pwhhmdsnyNXVeQUOADGf5O9XFaA9IkHejQZM&s= >> rax8HzzVk34s6HLibxE-gWtf-oeyghcc66PZ2sPOCLU&e= >> >> >> On Mon, Dec 11, 2017 at 3:34 PM Blake Watson <[email protected]> >> 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> >> > [email protected] >> > www.PennyMacUSA.com <http://www.pennymacusa.com/> >> > >> > > > > -- > > *Blake Watson* > > *PNMAC* > Application Development Manager > 5898 Condor Drive > Moorpark, CA 93021 > (805) 330.4911 x7742 > [email protected] > www.PennyMacUSA.com <http://www.pennymacusa.com/> > -- *Blake Watson* *PNMAC* Application Development Manager 5898 Condor Drive Moorpark, CA 93021 (805) 330.4911 x7742 [email protected] www.PennyMacUSA.com <http://www.pennymacusa.com/>
