Value vs. DateValue

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Value vs. DateValue

Blake Watson
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
[hidden email]
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Reply | Threaded
Open this post in threaded view
|

Re: Value vs. DateValue

Greg Woolsey
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://support.office.com/en-us/article/VALUE-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2


On Mon, Dec 11, 2017 at 3:34 PM Blake Watson <[hidden email]> 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>
> [hidden email]
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>
Reply | Threaded
Open this post in threaded view
|

Re: Value vs. DateValue

Blake Watson
DATEVALUE isn't currently supported, I realize, too, so...

On Tue, Dec 12, 2017 at 8:59 AM, Greg Woolsey <[hidden email]>
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 <[hidden email]>
> 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>
> > [hidden email]
> > www.PennyMacUSA.com <http://www.pennymacusa.com/>
> >
>



--

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
[hidden email]
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Reply | Threaded
Open this post in threaded view
|

Re: Value vs. DateValue

Blake Watson
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 <[hidden email]>
wrote:

> DATEVALUE isn't currently supported, I realize, too, so...
>
> On Tue, Dec 12, 2017 at 8:59 AM, Greg Woolsey <[hidden email]>
> 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 <[hidden email]>
>> 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>
>> > [hidden email]
>> > www.PennyMacUSA.com <http://www.pennymacusa.com/>
>> >
>>
>
>
>
> --
>
> *Blake Watson*
>
> *PNMAC*
> Application Development Manager
> 5898 Condor Drive
> Moorpark, CA 93021
> (805) 330.4911 x7742
> [hidden email]
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>



--

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
[hidden email]
www.PennyMacUSA.com <http://www.pennymacusa.com/>