Grouping.

[GEM]

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??

 

[Bob Phillips]

What do you mean by group? Do you mean just sort them, or do you mean sort
them and add row outlining?

 

[GEM]

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...

 

[Sean Timmons]

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]

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...

 

[EricG]

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(A2>0,A2<=0.33333333),1,IF(AND(A2>0.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

 

[Bob Phillips]

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.

 

[EricG]

Thanks, Bob. Definitely more readable than my brute force version.

 

[Sean Timmons]

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"