Bug 56269 - DateFormat - Rounding of fractionals
Summary: DateFormat - Rounding of fractionals
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.11-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Blocks: 54071
  Show dependency tree
Reported: 2014-03-15 13:03 UTC by Andreas Beeker
Modified: 2015-01-05 08:05 UTC (History)
0 users

different fractional rounding before save/load in Libre Office (4.94 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-03-15 13:03 UTC, Andreas Beeker
[PATCH] Round fractionals to seconds (15.67 KB, patch)
2014-05-04 21:11 UTC, Andreas Beeker
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Andreas Beeker 2014-03-15 13:03:01 UTC
Created attachment 31391 [details]
different fractional rounding before save/load in Libre Office

In our project we discovered some rounding problem with date fractions.
We've got some data from a database tool (TOAD) which minimal calculated fractions differently, but the result when opening the file was ok.
But the DateUtil class uses the real milliseconds and therefore output 03.01.2014 10:59:59 instead of 11:00:00 (fraction was 41642.45833321759)

After playing around with the fractions, it seems that at least Libre Office will round at 500 millisecs.
Actually when recalculate the second date in the example file, it will be saved as 10:59:59, but when reopening it will be 11:00:00.

MS Excel Viewer can't be forced to recalc via [1] and I haven't check with project laptops Excel version ...

Should this rounding be taken into account in the DateUtil class?

[1] http://apache-poi.1045710.n5.nabble.com/How-to-force-Excel-to-recalculate-on-opening-xlsx-td3249595.html
Comment 1 Andreas Beeker 2014-05-04 21:11:02 UTC
Created attachment 31588 [details]
[PATCH] Round fractionals to seconds

The patch adds another formatting option to the DateUtil class, to get rounded dates. I think, this should be the default, to have the date values rounded to seconds, but for the sake of compatibility I left the behavior unchanged.
Comment 2 Andreas Beeker 2014-05-04 21:18:23 UTC
applied with svn rev r1592419