Bug 83720 - PIVOTTABLE: Wrong date format in column field
Summary: PIVOTTABLE: Wrong date format in column field
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
4.3 all versions
Hardware: All All
: high normal
Assignee: Not Assigned
Keywords: bibisected, bisected, regression
: 117814 (view as bug list)
Depends on:
Blocks: Pivot-Table
  Show dependency treegraph
Reported: 2014-09-10 15:43 UTC by Jens S
Modified: 2022-02-24 09:37 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:
Regression By:

Simple sheet with pivot table (32.85 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-09-10 15:43 UTC, Jens S
screenshot of Pivot table layout (68.92 KB, image/jpeg)
2014-09-10 20:48 UTC, GerardF
test data (17.15 KB, application/octet-stream)
2018-09-22 04:05 UTC, yinlkwong
pivot table - column field doesn't show date format (20.29 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-11-04 16:28 UTC, Grobe

Note You need to log in before you can comment on or make changes to this bug.
Description Jens S 2014-09-10 15:43:06 UTC
Created attachment 106067 [details]
Simple sheet with pivot table

Date format not transferred correct to column header in pivot-table (regression)

Windows 8.1 64-bit
LibreOffice Danish

Create a simple table with 3 columns – Date; Description; Value – and some data.
Select the table and go to Data – Pivot-table.
Put the 'Date' in the column fields, 'Description' in row field and 'Value' in data field as sum.
The pivot-table is now made with date-format as dates serial number.

If I put the 'Date' in the row field, 'Description' in the column field there a no problem, and I can also use F12 to group the dates.

This error have no influence on pivot-tables made by earlier versions of LO – they update correct with this version of LO
Comment 1 GerardF 2014-09-10 20:48:16 UTC
Created attachment 106086 [details]
screenshot of Pivot table layout

Reproducible with!
Build ID: 652b807658a54cd2ccd04ebc6900d2cf1ce85015
TinderBox: Win-x86@39, Branch:master, Time: 2014-09-05_01:33:05

Sorry I don't have 4.3.1 available yet.

Since 4.3 we have a field named "Data".
This field is by default in column field.
His purpose is to have data by column (default) or row when having 2 (or more) fields in "Data fields".
Moving "Data" in row cure the problem.

Re-odering Column fields by dragging the "data" field after the "dato" field also works.

It seems that having this default Data field in 1st position in column fields prevents date format recognition.
Comment 2 ign_christian 2014-09-11 07:46:29 UTC
Reproduced from scratch with LO and under Ubuntu 12.04 x86.

Not reproduced with LO
Comment 3 Michael Weghorn 2015-02-01 20:41:02 UTC
bibisect result:
 c08fd0a6e2cf014989732351c624bede765d2375 is the first bad commit
commit c08fd0a6e2cf014989732351c624bede765d2375
Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com>
Date:   Mon May 12 06:03:39 2014 +0000

    commit b850adf13ec8fad5f1e49c06fbb1d81a546b4636
    Author:     Caolán McNamara <caolanm@redhat.com>
    AuthorDate: Sun Mar 30 21:24:11 2014 +0100
    Commit:     Caolán McNamara <caolanm@redhat.com>
    CommitDate: Mon Mar 31 09:23:16 2014 +0100
        coverity#1194932 Uncaught exception
        Change-Id: Ia1811b7ec040887079d90ac611316983c1e77b03


$ git bisect log
# bad: [423a84c4f7068853974887d98442bc2a2d0cc91b] source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e
# good: [752769ad0d2179e17ea0a08cc9004df7b890305b] source-hash-60c64b437c6678dd1d3fa3a6fc2b7da0480890d4
git bisect start 'latest' 'last42onmaster'
# good: [4fcd68ce4979f85fda4568f4b419a4b41d07345f] source-hash-2c4621c87ed3a7b19de195c21494c9a381e72b2e
git bisect good 4fcd68ce4979f85fda4568f4b419a4b41d07345f
# skip: [422186458e0b4db00c7e26b54d5b631f83bcad2a] source-hash-6948bf58ce181b17f60ef81f10205ef4dac50cc6
git bisect skip 422186458e0b4db00c7e26b54d5b631f83bcad2a
# bad: [a0b33bffff9c787dce71a13b344f06ae1453026b] source-hash-02e0be069e57e724c51f23e2e31b77657a6a1d3d
git bisect bad a0b33bffff9c787dce71a13b344f06ae1453026b
# good: [db29eee512d03b1dc0139b3752bbe7931b165377] source-hash-77b6c1602aaa0bd059077765e7fabb53d9e6ddeb
git bisect good db29eee512d03b1dc0139b3752bbe7931b165377
# good: [9d57c189d74551d2b3770cc81139ea10a62e672f] source-hash-5b5e62650354788e50b44f32c22b687b2018aba9
git bisect good 9d57c189d74551d2b3770cc81139ea10a62e672f
# skip: [7ed08df7d4b9b26f20fbd161ef7283e8c5f1e619] source-hash-82332ee1fc23b6fdccaf92149c0f2fa46fcdc4d6
git bisect skip 7ed08df7d4b9b26f20fbd161ef7283e8c5f1e619
# bad: [c4b8375c4f4cd507d1bb4913f46789cf354e78a5] source-hash-9359f4747181ae93f777f224a9f64a832d5b806c
git bisect bad c4b8375c4f4cd507d1bb4913f46789cf354e78a5
# bad: [043d1114b660bf322c82b6d4af3d7a6decf410b6] source-hash-de0309581b2a539e8ccf370ff0f054a56dba1c11
git bisect bad 043d1114b660bf322c82b6d4af3d7a6decf410b6
# good: [c4e590b6482131576747491ef9e0a2f9be6071aa] source-hash-1ad901464afa29c96682bde59a12f864fccd525a
git bisect good c4e590b6482131576747491ef9e0a2f9be6071aa
# bad: [5f470f9cc992302f16a0ad8b2680725ad5beec08] source-hash-7c4783f6a2cb7598ecc48f20379dad9784541d5b
git bisect bad 5f470f9cc992302f16a0ad8b2680725ad5beec08
# bad: [c08fd0a6e2cf014989732351c624bede765d2375] source-hash-b850adf13ec8fad5f1e49c06fbb1d81a546b4636
git bisect bad c08fd0a6e2cf014989732351c624bede765d2375
# first bad commit: [c08fd0a6e2cf014989732351c624bede765d2375] source-hash-b850adf13ec8fad5f1e49c06fbb1d81a546b4636

As mentioned in comment 1, the commit where this occurs first is also the first commit where the "Data" field is in the column fields.
Comment 4 Matthew Francis 2015-05-07 01:43:17 UTC
This began at the below commit.
Adding Cc: to quikee@gmail.com; Could you possibly take a look at this one? Thanks

    commit 4f1f8b8e993b98095bf50c9e432fb0400d318b1f
    Author:     Tomaž Vajngerl <tomaz.vajngerl@collabora.com>
    AuthorDate: Sun Mar 30 21:12:27 2014 +0200
    Commit:     Tomaž Vajngerl <tomaz.vajngerl@collabora.com>
    CommitDate: Mon Mar 31 09:44:44 2014 +0200
        pivot: new pivot table layout dialog
        This commit adds a new pivot table layout dialog which was implemented
        from scratch. Instead of custom controls this one uses list boxes
        for field entries which greatly reduces the code. It also fixes
        some visual and behaviour bugs and adds the possibility to edit the
        "Data" field.
        Change-Id: I6c01252acee5a2e8910e40e65904504d00e03057
Comment 5 Matthew Francis 2015-05-07 01:44:37 UTC
(Note: reproduction on master temporarily blocked by bug 91125)
Comment 6 Luca 2015-08-13 14:46:52 UTC
Experiencing the same problem with LO importing a pivot table from a table within a registered DB or a cell range in the same Calc document.

Formatting the pivot table has no effect when refreshed.

System: Linux Slackware64 14.1
Comment 7 Xisco Faulí 2015-09-02 13:28:42 UTC
Add 'bisected' to Keywords as the exact commit has been determined
Comment 8 Robinson Tryon (qubit) 2015-12-13 11:11:09 UTC Comment hidden (obsolete)
Comment 9 Xisco Faulí 2016-09-26 15:09:43 UTC
Adding Cc: to Tomaž Vajngerl
Comment 10 ThierryT 2016-12-30 15:05:05 UTC
Still exist on version 
Version: (x64)
Build ID: 8783ba61dfea562444cb6390e69aa8b3c5e91156
Threads CPU : 4; Version de l'OS :Windows 6.29; UI Render : par défaut; 
Locale : fr-FR (fr_FR); Calc: group
Comment 11 Xisco Faulí 2016-12-30 15:26:32 UTC
Version reflects the earliest version affected. Please do no change it to a newer one
Comment 12 Julien Nabet 2018-07-25 08:08:32 UTC
*** Bug 117814 has been marked as a duplicate of this bug. ***
Comment 13 yinlkwong 2018-09-22 04:05:49 UTC
Created attachment 145098 [details]
test data

Test data for lIBREOFFICE CALC VERSION 6.062.
Note the difference between PT1 and PT2.
A possible solution is to allow user to specify custom display format for DREC under  EDIT LAYOUT --> Options ---> format pivot table display for DREC
eg display only the date number instead of the full date 12/09/2018 or 43355
Comment 14 QA Administrators 2019-09-23 02:53:57 UTC Comment hidden (obsolete)
Comment 15 Oliver Brinzing 2019-09-23 16:52:59 UTC
reproducible with:

Version: (x64)
Build ID: 71ef762f21ada8c25aad2183065478171e985e8c
CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: threaded

> It seems that having this default Data field in 1st position in column fields
> prevents date format recognition.

-> removing "Data" field/or placing it after "DREC" makes date values visible
Comment 16 Grobe 2019-11-04 16:25:18 UTC
reproducible with:

Build ID: 1:6.0.7-0ubuntu0.18.04.2
CPU threads: 2; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: nb-NO (en_US.UTF-8); Calc: group

Also reproducible with:
LibreOffice Portable Fresh 6.3.2
on Windows 10 (unfortunately I don't have more specific details here at home)

Additional comment 1:
It is possible to manually set Columns fields as date format, but the format is lost again when refreshing the pivot table.

Aditional comment 2:
When refreshing pivot table, it shouldn't remove all formattings from coloumn fields, espechially if the number of fields doesn't change (i.e. no change in data set)

I made a testcase - see attachment
Comment 17 Grobe 2019-11-04 16:28:50 UTC
Created attachment 155508 [details]
pivot table - column field doesn't show date format
Comment 18 Phil 2020-01-21 08:59:10 UTC
Reproduced in LO 6.3

REM n°1:
if you only select the data to create the pivot table, aka do not select the headers row, then you readily see in the "Available Fields" that date is not recognized as a date, but as an integer.

REM n°2:
This seems to be a general problem with all the pivot table bugs related to formating loss, see metabug 103381
They are others bugs related to formating & pivot table, for instance 126557
Solving formating & pivot table issues would probably close a bunch of bug reports
Comment 19 s5t1e3v4e3m11@hotmail.com 2021-05-03 16:09:11 UTC
FYI: the bug is still there with:

Version: (x64) / LibreOffice Community
Build ID: 8a45595d069ef5570103caea1b71cc9d82b2aae4
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win
Locale: en-GB (en_GB); UI: en-US
Comment 20 Andreas Säger 2021-11-24 14:11:58 UTC
Still in 7.2.2. Affects data pilots from data sources too. Imported dates are displayed as serial day numbers.
Comment 21 Andreas Säger 2021-11-25 00:03:51 UTC
Format as date manually.
Group by days.
Comment 22 Justin L 2022-02-24 09:37:56 UTC
repro 7.4+

(In reply to Oliver Brinzing from comment #15)
> > It seems that having this default Data field in 1st position in column fields
> > prevents date format recognition.
> -> removing "Data" field/or placing it after "DREC" makes date values visible

This suggests to me that pivot tables need a lot of TLC.