+ Reply to Thread
Results 1 to 6 of 6

Filtering a pivot by time range

  1. #1
    Registered User
    Join Date
    01-30-2006
    Posts
    12

    Filtering a pivot by time range

    I have a sheet that is used with different data every week, after doing a 'master' pivot of all the data I then need to split the data down into three, filtered by time range (05:30 - 13:29, 13:30 - 21:29 & 21:30 - 05:30). I currently do this by manually filtering the master data then copy/paste into a separate sheet to create the pivot for each time period. Is there a way I can set up three pivots tables that each display the data for each respective time range?

    Thanks.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Filtering a pivot by time range

    Hi kootzz,

    If you are using the newer versions of Excel you can click on the row filter dropdown which should look like this: http://www.youtube.com/watch?v=JWShdF3RLJ4

    If you use the BETWEEN filter and select the START and END date and add on the time like "5/4/2013 5:30 AM" and "5/4/2013 1:29 PM" you should get what you desire.

    It isn't obvious that the Date Filters allow a time to be put after the date but it does.

    Hope that helps.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    01-30-2006
    Posts
    12

    Re: Filtering a pivot by time range

    Hi Marvin

    Thanks for that but I need to filter all days of the week but just those times. I have made a kind of workaround where I've added an extra column that allocates a value to each row based on the time value, I then use this to filter each pivot.

    Thanks again for your help.

  4. #4
    Forum Contributor
    Join Date
    11-11-2012
    Location
    Muscat, Oman
    MS-Off Ver
    Office 365
    Posts
    521

    Re: Filtering a pivot by time range

    Hello Koootzz, Did you try making 3 time groups?
    Best Regards/VKS

  5. #5
    Registered User
    Join Date
    01-30-2006
    Posts
    12

    Re: Filtering a pivot by time range

    Hi VKS

    No, I didn't, is this a better way of doing it?

  6. #6
    Forum Contributor
    Join Date
    11-11-2012
    Location
    Muscat, Oman
    MS-Off Ver
    Office 365
    Posts
    521

    Re: Filtering a pivot by time range

    Have done it once with start time of an event and it worked well for me. I can try if u can upload a sample data file. Best Regards/VKS

+ 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