Bug 49843

Summary: DateUtil.getJavaDate() fringe cases
Product: POI Reporter: alex <i8c.alex>
Component: POI OverallAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: 3.6-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   

Description alex 2010-08-30 07:39:00 UTC
The DateUtil.getJavaDate() service uses a calendar with the local timezone to transform an excel double into a java.util.Date.

Consider however the following example:
A user fills in "00:00" in a cell and formats it as a time. The user then "drags" the bottom right corner downwards until row "27" is reached. This row will read "02:00:00". 

Because excel starts counting on the 31st of december 1899, rows 25, 26 and 27 are times expressed on the 1st of january 1900. Suppose that the system default for timezone is "Europe/Paris". If i then wish to format that date with SimpleDateFormat with timezone set to the same default, everything works fine:
1899-12-31 22:00:00 CET 
1899-12-31 23:00:00 CET 
1900-01-01 00:00:00 CET 
1900-01-01 01:00:00 CET 
1900-01-01 02:00:00 CET 

However if we set the timezone of the simpledateformat to "UTC", we get the following:
1899-12-31 22:00:00 UTC 
1899-12-31 23:00:00 UTC 
1900-01-01 00:50:39 UTC 
1900-01-01 01:50:39 UTC 

If we set the system timezone to UTC (so the calendar uses UTC) then everything works again. Also note the results for default timezone "Europe/Brussels" with formatting for "UTC":
1899-12-31 22:00:00 UTC 
1900-01-01 00:00:00 UTC 
1900-01-01 01:00:00 UTC 
1900-01-01 02:00:00 UTC 

Can we get an overloaded getJavaDate() call where we can define the timezone for the calendar in question?

The attachment is an example xlsx with java code.
Comment 1 Nick Burch 2010-09-06 11:30:04 UTC
Excel dates don't have timezones attached to them

As such, when working with the dates, you need to remember this!

You should therefore either correct the timezone where known in your code, or ensure that your DateFormats are equally timezone naive. If you try to mix timezone naive and timezone specified things, it'll probably go wrong. There's a reason why Python warns you of this...