Following up on this: I changed the Mac Excel preference to use the Windows
1900 base date and all is well with the POI date calculations now.
Should this be addressed somewhere in the code or the docs, or is it
already?
Cheers,
Chris
On Sat, Jun 11, 2011 at 11:44 PM, Chris McCann <testflyjets@gmail.com>wrote:
> Yegor,
>
> I'll take a look at how the AnalysisToolPak functions are implemented to
> see if I can contribute.
>
> After I modified the spreadsheet to not use EOMONTH I was checking some
> other calculations and have come across a strange problem.
>
> Since I can't use EOMONTH I'm using this to get the first day of the next
> month.
>
> Assume A2 = 1/1/2011, then the function in B2 to get the first day of the
> next month is:
>
> B2: =DATE(YEAR(A2), MONTH(A2)+1, 1)
>
> This works fine in Excel and, I thought, in POI. But I noticed some
> formula values were not being returned correctly in POI, and after digging
> into it there seems to be an issue with the MONTH function.
>
> In Excel I get this:
>
> =MONTH(A2) => 1
>
> But POI says MONTH(A2) => 12.0.
>
> Say what? And that explains why a function that checks to see if two dates
> are equal is failing when the dates should be equal, and the dependent
> formulas are not returning the correct values.
>
> The serial date value for "1/1/2011" is 39082. If I use the DATE function
> above for B2 I get a serial number of 39083 and a date of 1/2/2011, which is
> quite wrong.
>
> Is there some trick to using MONTH and other date functions in Excel so
> that POI will evaluate them properly?
>
> I did look at the source for CalendarFieldFunction.java (I think that's
> what's handling MONTH), and I noticed a TODO about "fix 1900/1904 problem".
> I'm using a Mac, and the Excel docs do indicate that Mac and Windows systems
> use 1904 and 1900, respectively, for their serial dates. Could this be the
> problem, or at least part of it?
>
> Cheers,
>
> Chris
>
>
> On Sat, Jun 11, 2011 at 11:23 PM, Yegor Kozlov <yegor.kozlov@dinom.ru>wrote:
>
>> On Sun, Jun 12, 2011 at 8:28 AM, Chris McCann <testflyjets@gmail.com>
>> wrote:
>> > Thanks, Nick. Your response made me dig deeply into all of the
>> functions
>> > used in dependent cells. I found that we were using the EOMONTH
>> function to
>> > calculate the first/last day of a month. That function is part of the
>> > Analysis Toolpak, and I assume that's why it's not implemented. Is
>> there a
>> > list of implemented functions somewhere in the docs?
>> >
>>
>> Mappings for the AnalysisToolpack are defined here:
>>
>> http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
>>
>> At the moment POI implements only five ATP functions:
>>
>> ISEVEN
>> ISODD
>> MROUND
>> RANDBETWEEN
>> YEARFRAC
>>
>>
>> It should be easy to add support for EOMONTH, just follow the pattern:
>> create a subclass of FreeRefFunction and register it in
>> AnalysisToolPak.java.
>>
>> Patches are welcome. Let me know if you need guidance.
>>
>> > Changing our EOMONTH for a different function that is supported made
>> > everything work, so that problem is solved.
>> >
>> > Not being familiar with the code base in terms of how unimplemented
>> > functions are handled it seems to me it should be possible to at least
>> > surface the name of the requested, unimplemented function in the
>> exception
>> > message. That would be tremendously helpful for debugging.
>> >
>>
>> I'm going to look into the code and see if it is possible to propagate
>> function name into the exception message.
>>
>> Yegor
>>
>> 
>> To unsubscribe, email: userunsubscribe@poi.apache.org
>> For additional commands, email: userhelp@poi.apache.org
>>
>>
>
