+ Reply to Thread
Results 1 to 3 of 3

Filter recognizes dates, but Pivot Table filter does not

  1. #1
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Filter recognizes dates, but Pivot Table filter does not

    I can use a "regular" Excel filter on my field Date123 and it will recognize it as a date and allow me to filter by Month, for example, but when I use the same field in a Pivot Table, the Pivot Table Filter treats each date as a discrete data point and I can no longer filter by Month! I could understand if the regular Excel filter AND the Pivot Table filter behaved the same way - then I would assume there is garbage data, but in this case I'm baffled. The fields are ALL dates and there are 0 blanks. The pivot table does not include any blank rows.

    Thanks for any help.

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Filter recognizes dates, but Pivot Table filter does not

    Hi,

    Pivot table filter fields do not group dates. You can add the date field as a row field, then group (via right-click menu) it by year and month. That will create a new field for year and switch the existing field to months. You can then add both fields to the filter area. I personally like to use slicers with date fields for ease of use.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Filter recognizes dates, but Pivot Table filter does not

    Thanks - seems to do what I need!

+ 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. Macro to filter on dates in a pivot table
    By floricita in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-16-2016, 01:35 PM
  2. Pivot Table - Grouping dates using Report Filter
    By swaza in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-18-2015, 09:44 AM
  3. VBA Code to filter rolling dates from a pivot table
    By RavenousQbert in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-03-2014, 11:28 AM
  4. [SOLVED] VBA code to filter on Specific two dates ( pivot table)
    By tejal in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-24-2014, 03:36 PM
  5. Select Multiple Dates In Report Filter Pivot Table But Dates Are Not Static
    By biasedobserver in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-06-2014, 03:38 PM
  6. Filter Pivot Table between dates referenced in cell
    By tkleypas13 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-05-2013, 04:42 PM
  7. Auto update pivot table filter dates
    By kaitco in forum Excel General
    Replies: 5
    Last Post: 11-19-2010, 01:40 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