+ Reply to Thread
Results 1 to 6 of 6

Grouping events based on Time Intervals in Pivots

  1. #1
    Registered User
    Join Date
    01-22-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    4

    Grouping events based on Time Intervals in Pivots

    This is how I want my data..

    Time Slot Total
    9 am - 12 pm 338
    6 pm - 9 pm 305
    3 pm - 6 pm 288
    12 pm - 3 pm 250
    9 pm - 12 pm 187
    6 am - 9 am 149
    12 am - 3 am 70
    3 am - 6 am 35
    1622

    Is there a way to get this in pivot. I know that grouping dates etc is possible, bt am nt able to group acc to time slot. Working with Excel 2007. See attachment.
    Attached Files Attached Files

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

    Re: Grouping events based on Time Intervals in Pivots

    Hi hutch,

    Find the attachment with a helper column and your answer. You can manually resort the rows in the PT if you want.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Grouping events based on Time Intervals in Pivots

    @ MarvinP,

    I am curious to know about how you are showing (0-2,3-5,6-8,9-11 etc.,) in Row label when your helper column just have the one number in it

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

    Re: Grouping events based on Time Intervals in Pivots

    Hi,

    After doing the pivot with the Hour as row, right click on any of the row numbers and find the sub menu of "Group". Tell it the interval to group in is 3 as that is the number of hours the OP wanted grouped.

    I think it is shown here: http://www.contextures.com/xlPivot07.html

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Grouping events based on Time Intervals in Pivots

    Thanks for sharing your knowledge

    I was seen that method earlier but when I see your output I am searching the grouping in Field Settings lol.

    Nice solution

  6. #6
    Registered User
    Join Date
    01-22-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Grouping events based on Time Intervals in Pivots

    @MarvinP - That was super quick. Appreciate your revert and it sure was helpful. So simple and yet it failed me... Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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