Group By Time In Pivot Table

[kumara_faith]

Hi,

I have the following data:

Excel Workbook
	G	H	I	J	K	L	M
1	Date	Request Time	Serve Time	Serve End Time	Wait Period	Serve Period	Counter Number
2	03-01-17	8:37:53	8:40:59	8:41:08	0:03:06	0:00:09	6
3	03-01-17	8:46:51	8:50:51	9:01:06	0:04:00	0:10:15	3
4	03-01-17	9:00:38	9:02:22	9:15:26	0:01:44	0:13:04	6
5	03-01-17	9:48:03	9:49:05	10:39:35	0:01:02	0:50:30	6
6	03-01-17	10:15:23	10:16:27	10:31:37	0:01:04	0:15:10	2
7	03-01-17	10:15:28	10:31:38	10:31:46	0:16:10	0:00:08	2
8	03-01-17	10:31:24	10:31:46	11:05:50	0:00:22	0:34:04	2
9	03-01-17	10:37:30	10:39:35	11:12:07	0:02:05	0:32:32	6
10	03-01-17	10:41:10	10:41:50	11:20:24	0:00:40	0:38:34	3
11	03-01-17	10:55:13	11:05:50	11:11:42	0:10:37	0:05:52	2
12	03-01-17	11:05:32	11:11:42	11:24:48	0:06:10	0:13:06	2
13	03-01-17	11:18:52	11:20:25	11:37:27	0:01:33	0:17:02	3
14	03-01-17	11:19:14	11:27:45	11:28:24	0:08:31	0:00:39	6
15	03-01-17	11:21:55	11:37:28	12:36:00	0:15:33	0:58:32	3
16	03-01-17	11:26:45	11:28:24	11:37:14	0:01:39	0:08:50	6
17	03-01-17	11:34:25	12:36:00	12:45:43	1:01:35	0:09:43	3
18	03-01-17	11:36:19	11:37:14	11:52:23	0:00:55	0:15:09	6
19	03-01-17	11:36:52	11:52:23	11:52:51	0:15:31	0:00:28	6
20	03-01-17	11:43:16	11:52:51	12:01:07	0:09:35	0:08:16	6
21	03-01-17	11:44:27	12:44:28	12:58:15	1:00:01	0:13:47	2
22	03-01-17	11:49:50	12:01:07	12:11:05	0:11:17	0:09:58	6
23	03-01-17	11:49:58	12:58:15	12:58:50	1:08:17	0:00:35	2
24	03-01-17	12:04:23	12:58:50	12:59:05	0:54:27	0:00:15	2
25	03-01-17	12:07:33	12:11:05	12:24:17	0:03:32	0:13:12	6
26	03-01-17	12:12:19	12:59:05	12:59:19	0:46:46	0:00:14	2
27	03-01-17	12:12:58	12:24:17	12:28:28	0:11:19	0:04:11	6
28	03-01-17	12:22:37	12:28:28	12:48:38	0:05:51	0:20:10	6
29	03-01-17	12:22:41	12:59:19	13:01:56	0:36:38	0:02:37	2
30	03-01-17	12:22:57	12:48:39	12:51:10	0:25:42	0:02:31	6
31	03-01-17	12:23:01	12:51:10	12:59:26	0:28:09	0:08:16	6
32	03-01-17	12:23:05	13:01:56	13:16:54	0:38:51	0:14:58	2
33	03-01-17	12:27:09	13:16:54	13:27:00	0:49:45	0:10:06	2
34	03-01-17	12:39:29	13:27:00	13:27:12	0:47:31	0:00:12	2
35	03-01-17	12:59:10	12:59:26	13:08:53	0:00:16	0:09:27	6
36	03-01-17	13:05:19	13:08:53	13:18:23	0:03:34	0:09:30	6
37	03-01-17	13:11:32	13:27:12	13:53:09	0:15:40	0:25:57	2
38	03-01-17	13:17:55	13:18:23	13:29:43	0:00:28	0:11:20	6
39	03-01-17	13:51:57	13:53:09	13:58:32	0:01:12	0:05:23	2
40	03-01-17	13:54:39	13:58:32	14:04:22	0:03:53	0:05:50	2
41	03-01-17	13:55:21	14:04:22	14:04:31	0:09:01	0:00:09	2
42	03-01-17	13:55:30	14:04:31	14:04:48	0:09:01	0:00:17	2
43	03-01-17	14:02:22	14:04:48	14:08:52	0:02:26	0:04:04	2
44	03-01-17	14:02:41	14:05:45	14:11:48	0:03:04	0:06:03	3
45	03-01-17	14:08:43	14:08:52	14:50:56	0:00:09	0:42:04	2
46	03-01-17	14:24:35	14:35:11	14:42:16	0:10:36	0:07:05	3
47	03-01-17	14:48:10	14:50:47	15:04:00	0:02:37	0:13:13	3
48	03-01-17	15:02:40	15:03:06	15:19:35	0:00:26	0:16:29	2
49	03-01-17	15:04:20	15:04:30	15:06:18	0:00:10	0:01:48	3
50	03-01-17	15:12:56	15:18:24	15:37:05	0:05:28	0:18:41	6
51	03-01-17	15:15:40	15:15:42	15:30:56	0:00:02	0:15:14	3
52	03-01-17	15:23:49	15:30:57	15:45:36	0:07:08	0:14:39	3
53	03-01-17	15:24:33	15:36:48	15:39:34	0:12:15	0:02:46	2
54	03-01-17	15:29:47	15:39:34	16:09:32	0:09:47	0:29:58	2
55	03-01-17	15:37:40	15:45:37	16:04:30	0:07:57	0:18:53	3
Details Report_BSP

I wanted to group the Request Time in pivot table to show how many tickets in a intervals of every hour. However, the message is "Cannot Group That Selection

Excel Workbook
	A	B
3	Count of Counter Number	*
4	Request Time	Total
5	Request Time	1
6	8:29:34 AM	1
7	8:29:39 AM	1
8	8:32:35 AM	1
9	8:32:45 AM	1
10	8:32:50 AM	1
11	8:33:16 AM	1
12	8:33:54 AM	1
13	8:35:19 AM	1
14	8:35:33 AM	1
15	8:35:44 AM	1
16	8:35:58 AM	1
17	8:36:01 AM	1
18	8:36:13 AM	1
19	8:36:46 AM	1
Sheet1

Appreciate any help.

 

[Marcelo Branco]

Hi Marcelo,

Yes, something like that. Normally, if it worked in a pivot table, the ranges would be there e.g 8.00AM - 9.00AM and the number of ticket count will be 2.

It is just an example to show that grouping worked for me.
I don't know how to format the field like 8.00AM - 9.00AM after grouping - maybe someone else can help.
The number of tickets is correct (see second column)

M.