Bug 91365 - [FORMATTING] [Pivot table] Dates with User-defined format display wrong dates
Summary: [FORMATTING] [Pivot table] Dates with User-defined format display wrong dates
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.5.2 release
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:5.3.0
Keywords:
Depends on:
Blocks:
 
Reported: 2015-05-18 19:08 UTC by GerardF
Modified: 2016-10-21 09:45 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
File with minimal pivot table (30.68 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-05-18 19:08 UTC, GerardF 		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 GerardF 2015-05-18 19:08:01 UTC 
Created attachment 115698 [details]
File with minimal pivot table

When using a user-defined format for dates (not in the default drop-down list), Pivot Tables show erroneous values.

For exemple, 2015-02-15 with format AAAAMMDD: Pivot tables display 99991231 at opening the file and -84670530 after refresh.
Comment 1 m_a_riosv 2015-05-18 21:46:51 UTC 
Hi @GerardE,

reproducible Win7x64
Version: 4.4.3.2 Build ID: 88805f81e9fe61362df02b9941de8e38a9b5fd16

It seems as if the PT takes the values from the formatted result of the cell, however seems it works fine grouping dates [Ctrl+F12]
Comment 2 pierre-yves samyn 2015-05-19 14:32:49 UTC 
Hi

I also reproduce the Issue (windows 7) & Version: 4.4.2.2.

Also strange, you can restore the expected display with:

1. Open the attached spreadsheet
2. Right click D2, Refresh

After refresh, dates display -84670530 to -84670602

3. Select D2:D5
4. Format> Number Format > uncheck "Date" (or use Ctrl+Shift+3, or the icon in the Formatting toolbar)

Regards
Pierre-Yves
Comment 3 Eike Rathke 2016-06-16 15:10:40 UTC 
This seems to happen because the formatted date output somewhere is re-parsed to obtain the original date serial number, alas, there are no date delimiters so a "number" 20150215 is parsed, which, maybe including some rollover, results in the date -8467-05-30 again formatted with YYYYMMDD gives -84670530
Comment 4 Commit Notification 2016-06-17 11:40:02 UTC 
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=dc6bf6c8ae94fd89f30e39d63dfe02fdb042e98d

Resolves: tdf#91365 use underlying numeric value if available [API CHANGE]

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 5 pierre-yves samyn 2016-10-21 09:45:06 UTC 
Hi

Verified on windows 7/64 & Version: 5.3.0.0.alpha1+
Build ID: 8a796410ec8f440b4163b15b928347c499da7a8f
CPU Threads: 2; OS Version: Windows 6.1; UI Render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2016-10-20_23:07:21
Locale: fr-FR (fr_FR); Calc: group

Thank you
Regards
Pierre-Yves