| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping.
I have under column A times, for example
A1=3:45 PM A2=4:48 PM A3=7:12 AM ETC... How can I group these?? 1:00 AM - 1:59 AM, 2:00 AM - 2:59 AM etc... Is this possible?? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grouping.
What do you mean by group? Do you mean just sort them, or do you mean sort
them and add row outlining? -- __________________________________ HTH Bob "GEM" wrote in message ... I have under column A times, for example A1=3:45 PM A2=4:48 PM A3=7:12 AM ETC... How can I group these?? 1:00 AM - 1:59 AM, 2:00 AM - 2:59 AM etc... Is this possible?? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grouping.
Thanks Bob.
For example, in my report I have times of all day. 8:10 PM, 7:00 AM, 5:42 PM etc. I want to group them, 1:00 AM - 8:00 AM, 8:00 AM - 5:00 PM, 5:00 PM - 12:00 AM, all in a pivot table... 1:00 AM - 8:00 AM ---- 1587 calls 8:00 AM - 5:00 PM ---- 10897 calls 5:00 PM - 12:00 AM ----- 568 calls Something like this... "Bob Phillips" wrote: What do you mean by group? Do you mean just sort them, or do you mean sort them and add row outlining? -- __________________________________ HTH Bob "GEM" wrote in message ... I have under column A times, for example A1=3:45 PM A2=4:48 PM A3=7:12 AM ETC... How can I group these?? 1:00 AM - 1:59 AM, 2:00 AM - 2:59 AM etc... Is this possible?? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grouping.
Drop your times into your Row fields and any cell into the data field.
right-click on a time and select "Group and Show Detail" and "Group". you can then sort by Hours. "GEM" wrote: Thanks Bob. For example, in my report I have times of all day. 8:10 PM, 7:00 AM, 5:42 PM etc. I want to group them, 1:00 AM - 8:00 AM, 8:00 AM - 5:00 PM, 5:00 PM - 12:00 AM, all in a pivot table... 1:00 AM - 8:00 AM ---- 1587 calls 8:00 AM - 5:00 PM ---- 10897 calls 5:00 PM - 12:00 AM ----- 568 calls Something like this... "Bob Phillips" wrote: What do you mean by group? Do you mean just sort them, or do you mean sort them and add row outlining? -- __________________________________ HTH Bob "GEM" wrote in message ... I have under column A times, for example A1=3:45 PM A2=4:48 PM A3=7:12 AM ETC... How can I group these?? 1:00 AM - 1:59 AM, 2:00 AM - 2:59 AM etc... Is this possible?? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grouping.
It does it, but it groups by hours, 1, 2, 3, 4, 5 etc.
I want to group 1-8,8-5,5-10, etc... "Sean Timmons" wrote: Drop your times into your Row fields and any cell into the data field. right-click on a time and select "Group and Show Detail" and "Group". you can then sort by Hours. "GEM" wrote: Thanks Bob. For example, in my report I have times of all day. 8:10 PM, 7:00 AM, 5:42 PM etc. I want to group them, 1:00 AM - 8:00 AM, 8:00 AM - 5:00 PM, 5:00 PM - 12:00 AM, all in a pivot table... 1:00 AM - 8:00 AM ---- 1587 calls 8:00 AM - 5:00 PM ---- 10897 calls 5:00 PM - 12:00 AM ----- 568 calls Something like this... "Bob Phillips" wrote: What do you mean by group? Do you mean just sort them, or do you mean sort them and add row outlining? -- __________________________________ HTH Bob "GEM" wrote in message ... I have under column A times, for example A1=3:45 PM A2=4:48 PM A3=7:12 AM ETC... How can I group these?? 1:00 AM - 1:59 AM, 2:00 AM - 2:59 AM etc... Is this possible?? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Grouping.
You might try adding a helper column that pre-groups your times into the
"bins" that you want. For example, assuming your times are in Column A, this formula will group them as you suggest (1:00 AM - 8:00 AM, 8:00 AM - 5:00 PM, 5:00 PM - 12:00): =IF(AND(A20,A2<=0.33333333),1,IF(AND(A20.333333, A2<=0.7083333),2,3)) It worked on my test case. You get a 1, 2 or 3 depending on which "bin" the time falls into. In case you're wondering, 0.3333333 is the fraction of a full day represented by "8:00 AM", and "0" means just after midnight. Once you have your helper column, you can use it in your pivot table to count times that fall into each "bin". HTH, Eric "GEM" wrote: It does it, but it groups by hours, 1, 2, 3, 4, 5 etc. I want to group 1-8,8-5,5-10, etc... "Sean Timmons" wrote: Drop your times into your Row fields and any cell into the data field. right-click on a time and select "Group and Show Detail" and "Group". you can then sort by Hours. "GEM" wrote: Thanks Bob. For example, in my report I have times of all day. 8:10 PM, 7:00 AM, 5:42 PM etc. I want to group them, 1:00 AM - 8:00 AM, 8:00 AM - 5:00 PM, 5:00 PM - 12:00 AM, all in a pivot table... 1:00 AM - 8:00 AM ---- 1587 calls 8:00 AM - 5:00 PM ---- 10897 calls 5:00 PM - 12:00 AM ----- 568 calls Something like this... "Bob Phillips" wrote: What do you mean by group? Do you mean just sort them, or do you mean sort them and add row outlining? -- __________________________________ HTH Bob "GEM" wrote in message ... I have under column A times, for example A1=3:45 PM A2=4:48 PM A3=7:12 AM ETC... How can I group these?? 1:00 AM - 1:59 AM, 2:00 AM - 2:59 AM etc... Is this possible?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| grouping help!!!!!!! | Excel Worksheet Functions | |||
| Grouping | Excel Worksheet Functions | |||
| Grouping | Excel Discussion (Misc queries) | |||
| Grouping | Excel Discussion (Misc queries) | |||
| Grouping | Excel Discussion (Misc queries) | |||
Hybrid Mode
