+ Reply to Thread
Results 1 to 8 of 8

How to set a 'Date Filter' on a field in the 'Report Filter' section of a Pivot Table?

  1. #1
    Registered User
    Join Date
    06-13-2012
    Location
    Connecticut
    MS-Off Ver
    Excel 2010
    Posts
    35

    How to set a 'Date Filter' on a field in the 'Report Filter' section of a Pivot Table?

    In a pivot table, if a DATE field is in the ROWS section or in the COLUMNS section I can click on that section's filter options and choose 'Date Filters'. Among other things, this allows me to set a filter between any two dates- which is very handy.

    However, when I put a Date field in the FILTERS section there is no 'Date Filters' option. Why is this??!! I want to filter by date but I don't want the date to be in either the ROWS or COLUMNS section. Does anyone know a workaround that would allow me to filter by date but not include my Date field in the ROWS or COLUMNS section?

    I'm using Excel 2013. Thanks.

  2. #2
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494

    Re: How to set a 'Date Filter' on a field in the 'Report Filter' section of a Pivot Table?

    So are you saying the dates are to in the pivot table, if so then how will excel know what date the data relates to?

  3. #3
    Registered User
    Join Date
    06-13-2012
    Location
    Connecticut
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to set a 'Date Filter' on a field in the 'Report Filter' section of a Pivot Table?

    Thanks for your reply. Yes, the date field is in the pivot table but it's up top in the FILTERS section. You would know what dates the data relates to by opening the filter and seeing your filter selections. (The field would display the words 'Multiple Items' which is what appears when you have selected multiple items in a filter.)

    If you go to the 1:24 point of this youtube video

    http://www.youtube.com/watch?v=JWShdF3RLJ4

    you will see the date filters I want to use. The problem is, those date filters are only available if the date field is on the ROW or on the COLUMN. When the date field is moved to the FILTERS section the date filters are no longer available.

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: How to set a 'Date Filter' on a field in the 'Report Filter' section of a Pivot Table?

    unfortunately that's the way it is unless you have an olap pivot and a time dimension. since you have 2013 you could insert a Timeline-not exactly the same thing but quite useful
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  5. #5
    Registered User
    Join Date
    06-13-2012
    Location
    Connecticut
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to set a 'Date Filter' on a field in the 'Report Filter' section of a Pivot Table?

    Thanks for your reply. I actually do have an OLAP pivot and a time dimension. The time dimension allows me to multi-select specific months and years but it won't allow me to specify a time period BETWEEN 2 dates. And that's the capability I'd really like to have. It baffles me why the date filter would suddenly not be available just because a field is moved to the FILTER section of a pivot table- even in Excel 2013.

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: How to set a 'Date Filter' on a field in the 'Report Filter' section of a Pivot Table?

    well I guess only ms can tell you why

  7. #7
    Registered User
    Join Date
    06-13-2012
    Location
    Connecticut
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to set a 'Date Filter' on a field in the 'Report Filter' section of a Pivot Table?

    Indeed. I appreciate your answer though. How do I mark the post as 'answered'?

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: How to set a 'Date Filter' on a field in the 'Report Filter' section of a Pivot Table?

    click 'thread tools' at the top of the page then 'mark solved'

    I've asked a buddy of mine to see if he can find out why the filters don't work in the report filters area (but even if he does he may not be allowed to tell me!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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