+ Reply to Thread
Results 1 to 5 of 5

Pivot Table Filter: date field showing every single date instead of only dates in the tbl

  1. #1
    Registered User
    Join Date
    04-01-2013
    Location
    bethesda
    MS-Off Ver
    Excel 2010
    Posts
    6

    Pivot Table Filter: date field showing every single date instead of only dates in the tbl

    Have a pivot table referencing a data table, and want to filter pivot on report date field. There are only 3 report dates in the table (hundreds of rows for each report date), but when i select the filter it shows every single day (1-Jan, 2-Jan, 3-Jan, 4-Jan, etc) instead of showing the 3 different possible report dates that exist in the data. When i move the report date field to the columns or rows section of the pivot table it shows the 3 report date groupings correctly. There are no blank cells in the report date field, all cells are valid date formatting. None are text stored as number. In the same workbook i have other pivot table referencing the exact same data table and the report date filter works correctly and only displays the 3 available report dates in the drop down. I've check every setting in the pivot and cant figure out what i must have unchecked or changed. Why/How is the filter showing dates that do not exist in the run date column, and why arent they only showing the 3 run dates??? What am i doing wrong??? Really driving me crazy. Any insight or help would be appreciated.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Pivot Table Filter: date field showing every single date instead of only dates in the

    Try below.

    1. Stick date column into Row labels field.
    2. Right click on the field and "Group by" ->Days
    3. This should generate DateColumn (Day) in Row field.
    4. Move that field into filter.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    04-01-2013
    Location
    bethesda
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Pivot Table Filter: date field showing every single date instead of only dates in the

    Run Date Filter.jpg

    Thanks but it didnt work. The strange thing is there are other pivots in the workbook referring to the same data and have the Run Date in the filter and it views correctly. The other strange thing is i copied the data table to a new excel workbook and pasted values and did a new pivot table and the filter worked correctly there. I have a lot pivots and dashboards going on, so i really dont want to copy the data values to a clean workbook and recreate all the visuals. Trying to find out how to fix why it is showing dates that are not in the data table.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Pivot Table Filter: date field showing every single date instead of only dates in the

    Looks like the one to right is treating date field as text field.

    Hence, no date range filter or hierarchy.

    Try loading date column as text column and see what you get.

  5. #5
    Registered User
    Join Date
    05-09-2017
    Location
    San Diego, CA
    MS-Off Ver
    Standard 2013
    Posts
    3

    Re: Pivot Table Filter: date field showing every single date instead of only dates in the

    I was having the same issue, trying to filter the pivot table by the date. Only a handful of dates in the source data, but the filter listed every day of the year. The "Group By" suggestion didn't work, and the Column was properly formatted as a date. In another thread on a different subject, I was reading about how Excel defaults to sorting by their custom lists. I have no idea what sets of custom lists there are (except the one from the thread, months of the year made the name Jan sort to the top because Excel read it as Jan for January).
    ANYWAY, for some reason that made me look at my column name "Doc Date" and think, maybe Excel has an issue with the column name. I changed the name of the column to "Document Date" and suddenly the filter was working perfectly. Do not ask me why, or if it has to do with the custom lists thing, but if you are having this issue, at least it's something you can try.
    Now if only I could get the table to sort in data source order like it's supposed to...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Date filter not showing all dates
    By ManuMFK in forum Excel General
    Replies: 25
    Last Post: 09-26-2017, 12:12 AM
  2. [SOLVED] Filter dates in pivot table by entering date not selecting from drop down
    By bbeards in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 08-03-2017, 01:08 PM
  3. Sort a Date field in the Pivot Table Filter
    By wsexcel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-01-2017, 10:18 AM
  4. Pivot Table: Filter dates based on a date in another cell
    By justinua in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-26-2015, 05:47 PM
  5. [SOLVED] How to set a 'Date Filter' on a field in the 'Report Filter' section of a Pivot Table?
    By Rhino_dance in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 03-28-2013, 11:01 AM
  6. [SOLVED] Date Filter based on Cell Input - single date or range of dates
    By babaloo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-23-2012, 10:32 AM
  7. Replies: 0
    Last Post: 05-12-2010, 05:01 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1