|
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?? |
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?? |
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?? |
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?? |
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?? |
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?? |
Grouping.
You could use
IF(A2<--"08:00",1,IF(A2<=--"17:00",2,IF(A2<=--"22:00",3,4))) or =IF(A2<--"8:00 AM",1,IF(A2<=--"5:00 PM",2,IF(A2<=--"10:00 PM",3,4))) to make it more obvious. -- __________________________________ HTH Bob "EricG" wrote in message ... 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?? |
Grouping.
Thanks, Bob. Definitely more readable than my brute force version.
"Bob Phillips" wrote: You could use IF(A2<--"08:00",1,IF(A2<=--"17:00",2,IF(A2<=--"22:00",3,4))) or =IF(A2<--"8:00 AM",1,IF(A2<=--"5:00 PM",2,IF(A2<=--"10:00 PM",3,4))) to make it more obvious. -- __________________________________ HTH Bob "EricG" wrote in message ... 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?? |
Grouping.
Well, then why not SUMPRODUCT it all at once then...
=SUMPRODUCT(--(A2:A10000<=--"08:00"),--(A2:A10000<""))&" calls" =SUMPRODUCT(--(A2:A10000--"08:00"),--(A2:A10000<=--"17:00"))&" calls" =SUMPRODUCT(--(A2:A10000--"17:00"),--(A2:A10000<=--"22:00"))&" calls" =SUMPRODUCT(--(A2:A10000--"22:00"))&" calls" "EricG" wrote: Thanks, Bob. Definitely more readable than my brute force version. "Bob Phillips" wrote: You could use IF(A2<--"08:00",1,IF(A2<=--"17:00",2,IF(A2<=--"22:00",3,4))) or =IF(A2<--"8:00 AM",1,IF(A2<=--"5:00 PM",2,IF(A2<=--"10:00 PM",3,4))) to make it more obvious. -- __________________________________ HTH Bob "EricG" wrote in message ... 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?? |
| All times are GMT +1. The time now is 02:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com