Bug 48528 - An Error in Date() function
Summary: An Error in Date() function
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.5-FINAL
Hardware: PC Windows XP
: P1 major with 1 vote (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-01-11 21:01 UTC by Jaibabu
Modified: 2012-04-28 17:29 UTC (History)
1 user (show)


Attachments
Java file to generate Excel using POI3.5 (5.48 KB, text/x-java-source)
2010-01-21 00:03 UTC, Jaibabu 		Details
Template Excel file that POI reads (15.13 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2010-01-21 00:05 UTC, Jaibabu 		Details
Excel file created Using POI3.5 (12.97 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2010-01-21 00:06 UTC, Jaibabu 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Jaibabu 2010-01-11 21:01:05 UTC 
Hai Experts,

     While creating an Excel Template with POI 3.5-FINAL API the DATE() function works abnormally. I used the following function 

DATE(YEAR(A20),MONTH(A20)-1,DAY(A20)) 
      ie., DATE(YEAR(previouscolumn value),MONTH(PreviousColumn Value)-1, 
                                                        DAY(PreviousCOlumn Value))

     The above formula is working for some date ranges. If the previous column value is "February-09" it is correctly calculating the next value as "January-09"
using the above formula. But it fails after that. After "January-09" the next evaluation of the formula is not working properly saying "#Value" in the column with out any error such as "Not Yet Implemented". If i keep the cursor in the particular cell and i press enter without changing the value or formula then the excel calculates the value correctly. Hope this is a bug in POI 3.5-Final API. How can i overcome this.

Thanks
Jaibabu
Comment 1 Josh Micich 2010-01-20 08:54:01 UTC 
You seem to be describing unexpected behaviour from Excel, but we're going to need explicit details on how POI produced this workbook. Please upload some simple java code to produce this error (If relevant please add example input/output files).

Another observation that may be useful: Does Excel fix the value if you force a recalculation of the whole sheet (instead of re-parsing the single cell as you described)?
Comment 2 Jaibabu 2010-01-21 00:03:45 UTC 
Created attachment 24871 [details]
Java file to generate Excel using POI3.5
Comment 3 Jaibabu 2010-01-21 00:05:23 UTC 
Created attachment 24872 [details]
Template Excel file that POI reads
Comment 4 Jaibabu 2010-01-21 00:06:33 UTC 
Created attachment 24873 [details]
Excel file created Using POI3.5
Comment 5 Jaibabu 2010-01-21 00:06:56 UTC 
Hai Josh,

    Thanks for your response. I herewith attaching the Java file i used for writing Excel file using POI. (The java code reads a template excel file and generates the new Excel file with new values.)

    I am also attaching the "DemoTemplateFile.xlsx" which is used as a template file(from which the POI reads the format and formula) and "sampledemo.xlsx" file that POI generates using java code.

    In sampledemo.xlsx file please check the column "B12-B19" the Date formula is not evaluated correctly. But it evaluates the Date formula above that cell (B7-B11) correctly.
The formula used in that column is DATE( YEAR(  B6  ), MONTH( B6  )-1, DAY( B6 ) )
Assuming -1 is the problem in evaluation.

    If suppose if i use +1 instead it is evaluating correctly. Please refer Column (A7-A19).

   I used formula Evaluator to evaluate the formula cells in the Sheet. But i get the same result.

  Hope this may be useful for you.

Thanks
Jaibabu.J
Comment 6 Jaibabu 2010-01-21 00:08:41 UTC 
Hai Josh,

    Thanks for your response. I herewith attaching the Java file i used for writing Excel file using POI. (The java code reads a template excel file and generates the new Excel file with new values.)

    I am also attaching the "DemoTemplateFile.xlsx" which is used as a template file(from which the POI reads the format and formula) and "sampledemo.xlsx" file that POI generates using java code.

    In sampledemo.xlsx file please check the column "B12-B19" the Date formula is not evaluated correctly. But it evaluates the Date formula above that cell (B7-B11) correctly.
The formula used in that column is DATE( YEAR(  B6  ), MONTH( B6  )-1, DAY( B6 ) )
Assuming -1 is the problem in evaluation.

    If suppose if i use +1 instead it is evaluating correctly. Please refer Column (A7-A19).

   I used formula Evaluator to evaluate the formula cells in the Sheet. But i get the same result.

  Hope this may be useful for you.

Thanks
Jaibabu.J
Comment 7 Jaibabu 2010-01-24 20:00:20 UTC 
Hai Experts,

    I hope the explanation i gave was enough. Please have a look in this issue.

Thanks
Jaibabu.J
Comment 8 Jaibabu 2010-02-03 01:20:24 UTC 
Hai,

   Is there any thing need to explain about the problem more?

   Please reply.

Thanks
Jaibabu.J
Comment 9 brezelman 2012-01-04 12:43:47 UTC 
This bug is still present in poi 3.7 final. Excel interprets carries in the date formula function, e.g. DATE(2012;MONTH(TODAY())-1;1) is evaluated to 2011-12-01 in January; DATE(2012;MONTH(TODAY())-13;1) is evaluated to 2010-12-01 then. It's the same with positive values and for carries at the date value.
Comment 10 Nick Burch 2012-01-05 02:08:45 UTC 
Could someone please re-test with POI 3.8 beta 5 (the latest beta release), and confirm if the problem is fixed there or not?
Comment 11 brezelman 2012-01-05 07:34:14 UTC 
I checked the SVN code. Implementation of Date function doesn't deal with any carry. SVN history (log) of DateFunc.java only mentions elimination of dependency on HSSF and reduction of eclipse warnings after 3.7 release.
Comment 12 Nick Burch 2012-04-28 17:29:25 UTC 
Fixed in r1331796.