+ Reply to Thread
Results 1 to 11 of 11

Date Range Function in Pivot Table

  1. #1
    Registered User
    Join Date
    03-12-2012
    Location
    Manchester, England
    MS-Off Ver
    MS 365
    Posts
    50

    Date Range Function in Pivot Table

    Hi Everyone
    I am trying to develop a Pivot Table report that groups the dates in it's data in Months of the Year.

    For example there will be a list of transactions each with it's own date attached.
    What I want the report to do is allow me to pick a month as the filter and then only show data where the date falls in that month.
    At the moment it is only allowing me to pick the individual dates.
    I do have other fields that I need to filter by as well so any help appreciated.
    Thanks
    Chris

  2. #2
    Forum Contributor
    Join Date
    07-03-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    109
    Have you tried grouping the data and then selecting by months?

  3. #3
    Forum Contributor
    Join Date
    07-03-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    109

    Re: Date Range Function in Pivot Table

    Is this something like you was after?

    Just right click on one of your dates and select group, then choose the option you want your data grouped by.

    You can even amend the week start dates in case your working/finacial week starts on say a Wednesday.......

    Hope this helps
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-12-2012
    Location
    Manchester, England
    MS-Off Ver
    MS 365
    Posts
    50

    Re: Date Range Function in Pivot Table

    Not exactly as I need the "Date Field" to be in the Report Filter rather than Row Labels so I don't seem to get the grouping options.

  5. #5
    Registered User
    Join Date
    03-12-2012
    Location
    Manchester, England
    MS-Off Ver
    MS 365
    Posts
    50

    Re: Date Range Function in Pivot Table

    Not sure what you mean by that. The data is being populated via a data source rather than a data range in another worksheet.

  6. #6
    Forum Contributor
    Join Date
    07-03-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    109
    Can you post an example WB?

  7. #7
    Registered User
    Join Date
    03-12-2012
    Location
    Manchester, England
    MS-Off Ver
    MS 365
    Posts
    50

    Re: Date Range Function in Pivot Table

    Here is an example.
    I need to be able to Filter by Month and Year for separate reports.
    thanks for your help
    regards
    Chris
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    07-03-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    109

    Re: Date Range Function in Pivot Table

    Any closer?
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-12-2012
    Location
    Manchester, England
    MS-Off Ver
    MS 365
    Posts
    50

    Re: Date Range Function in Pivot Table

    Hi
    Yes that's doing the job. I'm not sure how you have achieved it though and I need to be able to do the same thing with the original report which I can't attach because it connects to an external data source.
    Can I trouble you for the method please?
    regards
    Chris

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

    Re: Date Range Function in Pivot Table

    you ought to be able to add the date as a row field then group it and then move the grouped field to the report filter zone
    Josie

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

  11. #11
    Forum Contributor
    Join Date
    07-03-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    109

    Re: Date Range Function in Pivot Table

    Hi,

    Joseph is exactly right, just move the date's into the row field, group by months or years, then shift it back into the report filter field

    Hope I helped you out with this

+ 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