Pivot Table grouping (Excel 97)

[Mudface]

Is there a way to group data in 6 hourly intervals? At the moment my data is grouped in days, but I'd like a bit more detail. Thanks for any help.

[Ian Mac]

On 2002-05-24 01:14, Mudface wrote:
Is there a way to group data in 6 hourly intervals? At the moment my data is grouped in days, but I'd like a bit more detail. Thanks for any help.

How is your Time/Date Column(s) Formatted?

I think you may need to add another column to your data with a formula that distingishes which 6 hour segment the Time/Dates fall.

Could you post a few lines of the(se) columns so I can I see which way would be best.

[Mudface]

Thanks, Ian. The Time/ Date column are formatted as hh:mm dd/mm/yy. Data is/ was recorded every 10 minutes, but there are some gaps (of a few hours or so) where the plant was off-line. Example data of the first few columns is as shown, minus the column headings: -


00:10 23/04/02 37.8 187.1 6.6
00:20 23/04/02 50.4 180.6
00:30 23/04/02 48.9 184.2 6.7
00:40 23/04/02 36.4 183.7 6.6
00:50 23/04/02 36.4 184.8 7.6
01:00 23/04/02 37.2 185.9 7.6
01:10 23/04/02 37.5 186.3 7.6
01:20 23/04/02 37.8 186.8 7.6
01:30 23/04/02 38.7 187.4 7.1
01:40 23/04/02 37.6 187.6 7.2
01:50 23/04/02 37.1 187.5 7.1
02:00 23/04/02 37 187.1 7.1
02:10 23/04/02 37.2 186.8 7.1

nb time and date is in the same column.


[ This Message was edited by: Mudface on 2002-05-24 02:38 ]

[Ian Mac]

Here a starter for ten.

I've added a Column to the end of the data with the formula:

=VLOOKUP(TEXT(A2,"h:mm")+0,{0;0.25;0.5;0.75},1)

to separate the 6hr segments. The following is the result I get when use in a pivot table.

Microsoft Excel - Book2 ___Running: xl97 : OS = Windows (32-bit) NT 4.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
J51 =
	A	B	C	D	E	F	G	H	I	J
1	Date/Time	Field1	Field2	Field3	Segment
2	23/04/02 00:10	37.8	187.1	6.6	:alert('=VLOOKUP(TEXT(A2,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00
3	23/04/02 01:50	50.4	180.6	6.6	:alert('=VLOOKUP(TEXT(A3,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00
4	23/04/02 03:30	63	174.1	6.6	:alert('=VLOOKUP(TEXT(A4,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00
5	23/04/02 05:10	75.6	167.6	6.6	:alert('=VLOOKUP(TEXT(A5,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00		Date/Time	Segment	Data	Total
6	23/04/02 06:50	88.2	161.1	6.6	:alert('=VLOOKUP(TEXT(A6,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 06:00		23-Apr	0:00	Sum of Field1	4158
7	23/04/02 08:30	100.8	154.6	6.6	:alert('=VLOOKUP(TEXT(A7,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 06:00				Sum of Field2	334.2
8	23/04/02 10:10	113.4	148.1	6.6	:alert('=VLOOKUP(TEXT(A8,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 06:00				Sum of Field3	79.2
9	23/04/02 11:50	126	141.6	6.6	:alert('=VLOOKUP(TEXT(A9,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 06:00			6:00	Sum of Field1	2016
10	23/04/02 13:30	138.6	135.1	6.6	:alert('=VLOOKUP(TEXT(A10,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 12:00				Sum of Field2	612.8
11	23/04/02 15:10	151.2	128.6	6.6	:alert('=VLOOKUP(TEXT(A11,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 12:00				Sum of Field3	52.8
12	23/04/02 16:50	163.8	122.1	6.6	:alert('=VLOOKUP(TEXT(A12,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 12:00			12:00	Sum of Field1	1776.6
13	23/04/02 18:30	176.4	115.6	6.6	:alert('=VLOOKUP(TEXT(A13,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 18:00				Sum of Field2	323.1
14	23/04/02 20:10	189	109.1	6.6	:alert('=VLOOKUP(TEXT(A14,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 18:00				Sum of Field3	39.6
15	23/04/02 21:50	201.6	102.6	6.6	:alert('=VLOOKUP(TEXT(A15,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 18:00			18:00	Sum of Field1	2721.6
16	23/04/02 23:30	214.2	96.1	6.6	:alert('=VLOOKUP(TEXT(A16,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 18:00				Sum of Field2	248.8
17	24/04/02 01:10	226.8	89.6	6.6	:alert('=VLOOKUP(TEXT(A17,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00				Sum of Field3	52.8
18	24/04/02 02:50	239.4	83.1	6.6	:alert('=VLOOKUP(TEXT(A18,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00		23-Apr Sum of Field1			10672.2
19	24/04/02 04:30	252	76.6	6.6	:alert('=VLOOKUP(TEXT(A19,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00		23-Apr Sum of Field2			1518.9
20	24/04/02 06:10	264.6	70.1	6.6	:alert('=VLOOKUP(TEXT(A20,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 06:00		23-Apr Sum of Field3			224.4
21	24/04/02 07:50	277.2	63.6	6.6	:alert('=VLOOKUP(TEXT(A21,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 06:00		24-Apr	0:00	Sum of Field1	2305.8
22	24/04/02 09:30	289.8	57.1	6.6	:alert('=VLOOKUP(TEXT(A22,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 06:00				Sum of Field2	50.1
23	24/04/02 11:10	302.4	50.6	6.6	:alert('=VLOOKUP(TEXT(A23,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 06:00				Sum of Field3	39.6
24	24/04/02 12:50	315	44.1	6.6	:alert('=VLOOKUP(TEXT(A24,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 12:00			6:00	Sum of Field1	3427.2
25	23/04/02 00:10	327.6	37.6	6.6	:alert('=VLOOKUP(TEXT(A25,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00				Sum of Field2	-115.2
26	23/04/02 01:50	340.2	31.1	6.6	:alert('=VLOOKUP(TEXT(A26,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00				Sum of Field3	52.8
27	23/04/02 03:30	352.8	24.6	6.6	:alert('=VLOOKUP(TEXT(A27,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00			12:00	Sum of Field1	919.8
[B]28:alert('=VLOOKUP(TEXT(A30,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 06:00		00/01/00 12:00		Total Sum of Field1			17325
34	23/04/02 15:10	441	-20.9	6.6	:alert('=VLOOKUP(TEXT(A34,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 12:00		Total Sum of Field2			1392.5
35	23/04/02 16:50	453.6	-27.4	6.6	:alert('=VLOOKUP(TEXT(A35,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 12:00		Total Sum of Field3			330
36	23/04/02 18:30	466.2	-33.9	6.6	:alert('=VLOOKUP(TEXT(A36,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 18:00
37	23/04/02 20:10	478.8	-40.4	6.6	:alert('=VLOOKUP(TEXT(A37,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 18:00
38	23/04/02 21:50	491.4	-46.9	6.6	:alert('=VLOOKUP(TEXT(A38,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 18:00
39	23/04/02 23:30	504	-53.4	6.6	:alert('=VLOOKUP(TEXT(A39,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 18:00
40	24/04/02 01:10	516.6	-59.9	6.6	:alert('=VLOOKUP(TEXT(A40,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00
41	24/04/02 02:50	529.2	-66.4	6.6	:alert('=VLOOKUP(TEXT(A41,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00
42	24/04/02 04:30	541.8	-72.9	6.6	:alert('=VLOOKUP(TEXT(A42,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00
43	24/04/02 06:10	554.4	-79.4	6.6	:alert('=VLOOKUP(TEXT(A43,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 06:00
44	24/04/02 07:50	567	-85.9	6.6	:alert('=VLOOKUP(TEXT(A44,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 06:00
45	24/04/02 09:30	579.6	-92.4	6.6	:alert('=VLOOKUP(TEXT(A45,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 06:00
46	24/04/02 11:10	592.2	-98.9	6.6	:alert('=VLOOKUP(TEXT(A46,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 06:00
47	24/04/02 12:50	604.8	-105.4	6.6	:alert('=VLOOKUP(TEXT(A47,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 12:00
48	23/04/02 00:10	617.4	-111.9	6.6	:alert('=VLOOKUP(TEXT(A48,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00
49	23/04/02 01:50	630	-118.4	6.6	:alert('=VLOOKUP(TEXT(A49,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00
50	23/04/02 03:30	642.6	-124.9	6.6	:alert('=VLOOKUP(TEXT(A50,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00
51	23/04/02 05:10	655.2	-131.4	6.6	:alert('=VLOOKUP(TEXT(A51,%22h:mm%22)+0,{0;0.25;0.5;0.75},1)')>00/01/00 00:00
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.27]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


It doesn't look that pretty but I hope you get the idea.

Is this what you needed?

_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-05-24 03:27 ]

[Mudface]

Excellent, cheers Ian , I'll give that a whirl. Strange that you can group by days, no problem, but there's no (automatic) facility for what you've done above, in '97 at least.