5

My Excel is in Finnish, but don't let that bother you...

First Report Filter "Kupi" has only number values in my source table, for example 643203, 3533, 253244, etc. How ever in Pivot's "Report Filter" all those values are converted to date values MONTH yyyy. How do I reformat the filter values to respect the original cell formatting?!

The same problem is with actual date values in my source table when using "Report Filter" in Pivot table. In my source data my dates are in format: dd.mm.yyyy and for some reason in Pivot's "Report Filter" all dates are shown in MONTH yyyy-format?! Why is that and what do I need to do to fix this?

Pivot table - Report Filter

ADDED MORE INFORMATION:

Structure for this Excel workbook is: one source table and about hundred pivot tables from that one source.

I use this Workbook daily and all these Pivot tables have worked before correctly. About a week ago when I opened the workbook all numeric data -cells had changed into date-format from no apparent reason?! I tried to restore to older versions of the document, but no bonus. Then I just manually corrected cell formatting of the source table and it looked just like before, but for some reason after that incident none of the pivot tables have worked correctly and even if I create new Pivot tables from my source table - they still have corrupted cell formatting, which do not respect the source table...

Below you can see a clip from my source table.

Data from source table.

Below you can see that even though cell format in source table for column "Kupi" has been set to "Number", in Pivot table it is shown as date and there seems to be no way edit cell formatting in Pivot's Filters.

Column Kupi

Below you can see how Column "Tilattu" is shown in Pivot's filters, even though in source data cell format is set to be "dd.m.yyyy". As you can see it is impossible to filter for a specific date!

Column tilattu

EVEN MORE INFORMATION ADDED:

I've been testing workbook for a couple of hours now and it seems that no matter what I add to Pivot table, all cell formats defaults to "date"!? This is so frustrating.

Example:

  1. I created new Pivot table into new sheet.
  2. I filtered there date from november 2011
  3. I have two currency columns and sum of all rows in the last row of the Pivot table
  4. I selected an empty cell and wrote "=" clicked both currency sums and pressed enter to get the total into to empty cell outside of the Pivot table
  5. Even that cell in new sheet formatted into date!!!

Currency total as date.

1
  • An example excel file with a simplified pivot report (minimum columns) already set up showing the problem would help with debugging. It does not need to be real data.
    – Luke
    Nov 5, 2013 at 2:32

1 Answer 1

0

These numbers look like typical "sequential serial number" format of the Date function, meaning the number of the day.

The cell that displays "MONTH yyyy" is formatted as a date, probably a custom date format since this is not a standard display format. Or maybe you have used the Group By option of the Pivot Table and have chosen "Group by Month".

If you wish to change the display format of a cell, see Microsoft's Format a date the way you want.

We need to know more about your datasheets to give better advice.

2
  • For every other field in Pivot table it seems to possible to change cell formatting but not in "Report Filter"-fields... And the problem is that Pivot table is not respecting the cell formatting of the source table. (I've added much more information to my original post).
    – Damiqib
    Nov 2, 2013 at 20:35
  • The text at Change the layout and format of a PivotTable report and especially the section Change or remove formatting might help.
    – harrymc
    Nov 2, 2013 at 21:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .