Pvtbl Grouping: Time Duration

[Mike]

Hi everyone,

I have a spread sheet with a column that has time duration, for example:

Time to Action
1:42:43
0:50:26
32:03:16
30:24:33
31:27:52
31:36:11
29:15:58
41:51:16
41:18:21
41:19:59
42:18:46
23:15:28
23:36:03
28:31:47
21:22:00
26:00:00
22:28:18
18:35:40
32:22:09
20:24:53
19:15:34
36:39:42
2:07:46
17:38:52
19:09:03
17:57:27
24:33:28
19:18:24

I have a whole bunch of other data including this one being fed into a nice
little PivotTable. Each time is related to unique ticket number, I need to
group each hour here. When I group by Hours, it assumes that I mean 24 hours
only... needless to say, the data being displayed in the table is incorrect
because I have duration time stamps greater than 24 hours.

My question is: How do create a custom grouping on a PivotTable that will
work in 2003? Or is there a built in function I am missing?

Thank you!

 

[Herbert Seidenberg]

Group by day and hour:
http://www.freefilehosting.net/download/3dckl

 

[Debra Dalgleish]

You could add a column to your source data, and calculate the number of
hours in each record. Then, use a lookup table to return the group for
that number of hours.

For example, with a lookup table named HoursLookup:

0 00 - 09
10 10 - 19
20 20 - 29
30 30 - 39
40 40-49
50 50+

Use the following formula to calculate the grouping for the time to
action in cell A2:

=VLOOKUP(A2*24,HoursLookup,2)

Copy the formula down to the last row of data.

Add that field to the pivot table, as the first field in the row area,
instead of using the Time to Action field.

 

[Mike]

Hi Herbert and Debra,

Thanks very much for the responses! I actually, a couple of hours after
posting the question just decided to decimal time using the following
equation:

=IF(DAY(I2)>0,((I2-INT(I2))*24)+(DAY(I2)*24),(I2-INT(I2))*24)

So when I group it, it comes out like this on the PVT:
2-3
3-4
4-5
5-6
6-7
7-8
8-9
9-10
10-11
11-12
12-13
etc...

This was way past quiting time when I figured it out... and I can't even
remember how I did figure it out I was so tired! LOL

Hope you guys can use this in the future!
Cheers,
Mike