Bug 39472

Summary: Wrong behaviour when setting cell value to dates between 1900-01-01 and 1900-02-28
Product: POI Reporter: Cosma Colanicchia <cosmacol>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED WONTFIX    
Severity: minor    
Priority: P2    
Version: 2.5-FINAL   
Target Milestone: ---   
Hardware: Other   
OS: All   

Description Cosma Colanicchia 2006-05-03 15:42:56 UTC
Try to set a cell value using the HSSFCell.setCellValue(java.util.Date) to a
date that is between 1900-01-01 and 1900-02-27, you'll get a value with a +1
than the correct one.

All dates between 1900-01-01 and 1900-01-27 get shifted with a +1.

From day 61 onwards (1900-03-01) the problem doesn't appear due to an Excel
strange behaviour: it seems to consider 1900 as a lap year (while it isn't), so
it diplay day 60 as 29/02/1900. So the two problems compensate each other.

Ex.
getExcelDate(1900-01-01) --> 2  (excel display as Jan 1, 1900)
getExcelDate(1900-01-02) --> 3  (excel display as Jan 2, 1900)
getExcelDate(1900-01-03) --> 4  (excel display as Jan 3, 1900)
...
getExcelDate(1900-02-27) --> 59 (excel display as Feb 28, 1900)
getExcelDate(1900-02-28) --> 60 (excel display as Feb 29, 1900!!)
getExcelDate(1900-03-01) --> 61 (excel display as Mar 03, 1900)

A fix should take into account the excel problem without breaking the first two
months.
Comment 1 Cosma Colanicchia 2006-05-04 07:44:18 UTC
(In reply to comment #0)
Only to correct an error in the description, the ex. table should look like this:

Ex.
getExcelDate(1900-01-01) --> 2  (excel display as Jan 2, 1900)
getExcelDate(1900-01-02) --> 3  (excel display as Jan 3, 1900)
getExcelDate(1900-01-03) --> 4  (excel display as Jan 4, 1900)
...
getExcelDate(1900-02-27) --> 59 (excel display as Feb 28, 1900)
getExcelDate(1900-02-28) --> 60 (excel display as Feb 29, 1900!!)
getExcelDate(1900-03-01) --> 61 (excel display as Mar 03, 1900)
Comment 2 David Fisher 2008-12-30 10:38:44 UTC
The strange behavior here in Excel is historical and Microsoft chose to keep Excel compatible with Lotus 1-2-3 and MultiPlan at the beginning.

I don't think we care to burden date format portions of the api with tests for these exceedingly rare cases.

If this does bother you then switch your Excel files over to "1904" date format.

Please see the following:

http://support.microsoft.com/default.aspx?scid=kb;en-us;214058

http://support.microsoft.com/default.aspx?scid=kb;en-us;214019

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q214094#6

Another alternative for you would be to simply set the cell as a number and then apply a date format to it.