Allocate the Hours based on Interval covered

[Airam]

Hi Everyone!

Asking for your expertise on this. My raw file consists of total hours covered per specific instance of Status/aux state. I want to identify how many of these hours are allocated based on a 15 minute interval. Please see attached excel file for you guidance.

Distribute hours in an Interval.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	RAW DATA										Outcome
2	NAME	StatusDateTime	Status	EndDateTime	StateDuration (milliseconds)	State Duration (sec)	DATE	Time Start	Time End		Interval	26-Jun	27-Jun	28-Jun
3	John Doe	06/26/2023 09:14	Available	6/26/2023 09:15	34.266	0:00:34	06/26/2023	09:14:48	09:15:22			Hours	Hours	Hours
4	John Doe	06/26/2023 09:15	OnCall	6/26/2023 09:58	2578.498	0:42:58	06/26/2023	09:15:22	09:58:21		09:00
5	John Doe	06/26/2023 09:58	Available	6/26/2023 10:12	841.913	0:14:02	06/26/2023	09:58:21	10:12:23		09:15
6	John Doe	06/26/2023 10:12	Ticket	6/26/2023 10:18	391.372	0:06:31	06/26/2023	10:12:23	10:18:54		09:45
7	John Doe	06/26/2023 10:18	Break	6/26/2023 10:26	441.315	0:07:21	06/26/2023	10:18:54	10:26:15		10:00
8	John Doe	06/26/2023 10:26	Outbound	6/26/2023 10:26	9.602	0:00:10	06/26/2023	10:26:15	10:26:25		10:15
9	John Doe	06/26/2023 10:26	OnCall	6/26/2023 11:22	3384.429	0:56:24	06/26/2023	10:26:25	11:22:49		10:30
10	John Doe	06/26/2023 11:22	Outbound	6/26/2023 11:23	45.092	0:00:45	06/26/2023	11:22:49	11:23:35		10:45
11	John Doe	06/26/2023 11:23	Break	6/26/2023 11:39	949.956	0:15:50	06/26/2023	11:23:35	11:39:24		11:00
12	John Doe	06/26/2023 11:39	Outbound	6/26/2023 11:39	3.706	0:00:04	06/26/2023	11:39:24	11:39:28		11:15
13	John Doe	06/26/2023 11:39	OnCall	6/26/2023 13:16	5818.733	1:36:59	06/26/2023	11:39:28	13:16:27		11:30
14	John Doe	06/26/2023 13:16	Outbound	6/26/2023 13:17	35.662	0:00:36	06/26/2023	13:16:27	13:17:03		11:45
15	John Doe	06/27/2023 09:55	Available	6/27/2023 09:55	19.809	0:00:20	06/27/2023	09:55:27	09:55:47		12:00
16	John Doe	06/27/2023 09:55	OnCall	6/27/2023 10:53	3471.399	0:57:51	06/27/2023	09:55:47	10:53:38		12:15
17	John Doe	06/27/2023 10:53	Available	6/27/2023 10:53	5.622	0:00:06	06/27/2023	10:53:38	10:53:44		12:30
18	John Doe	06/27/2023 10:53	Break	6/27/2023 11:10	990.628	0:16:31	06/27/2023	10:53:44	11:10:15		12:45
19	John Doe	06/27/2023 11:10	Break	6/27/2023 11:17	426.087	0:07:06	06/27/2023	11:10:15	11:17:21		13:00
20	John Doe	06/27/2023 11:17	Outbound	6/27/2023 11:17	18.036	0:00:18	06/27/2023	11:17:21	11:17:39		13:15
21	John Doe	06/27/2023 11:17	OnCall	6/27/2023 13:08	6634.729	1:50:35	06/27/2023	11:17:39	13:08:14		13:30
22	John Doe	06/27/2023 13:08	Outbound	6/27/2023 13:08	21.414	0:00:21	06/27/2023	13:08:14	13:08:35		13:45
23	John Doe	06/27/2023 13:08	Lunch	6/27/2023 13:40	1914.735	0:31:55	06/27/2023	13:08:35	13:40:30		14:00
24	John Doe	06/27/2023 13:40	Outbound	6/27/2023 13:42	113.715	0:01:54	06/27/2023	13:40:30	13:42:23		14:15
25	John Doe	06/27/2023 13:54	Available	6/27/2023 13:56	107.198	0:01:47	06/27/2023	13:54:53	13:56:41		14:30
26	John Doe	06/27/2023 13:58	Available	6/27/2023 13:59	7.367	0:00:07	06/27/2023	13:58:56	13:59:03		14:45
27	John Doe	06/27/2023 13:59	OnCall	6/27/2023 14:36	2219.618	0:37:00	06/27/2023	13:59:03	14:36:03		15:00
28	John Doe	06/27/2023 14:36	Coaching	6/27/2023 14:36	25.852	0:00:26	06/27/2023	14:36:03	14:36:29		15:15
29	John Doe	06/27/2023 14:36	Meeting	6/27/2023 14:55	1150.479	0:19:10	06/27/2023	14:36:29	14:55:39		15:30
30	John Doe	06/27/2023 14:55	Outbound	6/27/2023 14:55	7.593	0:00:08	06/27/2023	14:55:39	14:55:47		15:45
31	John Doe	06/27/2023 14:55	OnCall	6/27/2023 15:42	2801.012	0:46:41	06/27/2023	14:55:47	15:42:28		16:00
32	John Doe	06/27/2023 15:42	Outbound	6/27/2023 15:45	209.283	0:03:29	06/27/2023	15:42:28	15:45:57		16:15
33	John Doe	06/27/2023 15:45	Break	6/27/2023 16:01	921.68	0:15:22	06/27/2023	15:45:57	16:01:19		16:30
34	John Doe	06/27/2023 16:01	Outbound	6/27/2023 16:01	16.862	0:00:17	06/27/2023	16:01:19	16:01:36		16:45
35	John Doe	06/27/2023 16:01	OnCall	6/27/2023 17:30	5360.989	1:29:21	06/27/2023	16:01:36	17:30:57		17:00
36	John Doe	06/27/2023 17:30	Outbound	6/27/2023 17:31	35.914	0:00:36	06/27/2023	17:30:57	17:31:33		17:15
37	John Doe	06/28/2023 14:00	Available	6/28/2023 14:01	15.988	0:00:16	06/28/2023	14:00:53	14:01:09		17:30
38	John Doe	06/28/2023 14:01	OnCall	6/28/2023 15:37	5760.993	1:36:01	06/28/2023	14:01:09	15:37:10		17:45
39	John Doe	06/28/2023 15:37	Available	6/28/2023 15:37	17.865	0:00:18	06/28/2023	15:37:10	15:37:28		18:00
40	John Doe	06/28/2023 15:37	OnCall	6/28/2023 15:56	1126.481	0:18:46	06/28/2023	15:37:28	15:56:14		18:15
41	John Doe	06/28/2023 15:56	Available	6/28/2023 15:56	22.496	0:00:22	06/28/2023	15:56:14	15:56:37		18:30
42	John Doe	06/28/2023 15:56	Ticket	6/28/2023 16:02	331.802	0:05:32	06/28/2023	15:56:37	16:02:09		18:45
43	John Doe	06/28/2023 16:02	Outbound	6/28/2023 16:03	99.543	0:01:40	06/28/2023	16:02:09	16:03:48		19:00
44	John Doe	06/28/2023 16:03	Break	6/28/2023 16:19	968.996	0:16:09	06/28/2023	16:03:48	16:19:57		19:15
45	John Doe	06/28/2023 16:19	Break	6/28/2023 16:27	423.018	0:07:03	06/28/2023	16:19:57	16:27:00		19:30
46	John Doe	06/28/2023 16:27	Outbound	6/28/2023 16:27	2.171	0:00:02	06/28/2023	16:27:00	16:27:02		19:45
47	John Doe	06/28/2023 16:27	OnCall	6/28/2023 16:42	935.912	0:15:36	06/28/2023	16:27:02	16:42:38		20:00
48	John Doe	06/28/2023 16:42	Outbound	6/28/2023 16:42	13.061	0:00:13	06/28/2023	16:42:38	16:42:51		20:15
49	John Doe	06/28/2023 16:42	Break	6/28/2023 16:44	125.979	0:02:06	06/28/2023	16:42:51	16:44:57		20:30
50	John Doe	06/28/2023 16:44	Outbound	6/28/2023 16:44	1.426	0:00:01	06/28/2023	16:44:57	16:44:59		20:45
51	John Doe	06/28/2023 16:44	OnCall	6/28/2023 17:48	3837.278	1:03:57	06/28/2023	16:44:59	17:48:56		21:00
52	John Doe	06/28/2023 17:48	Outbound	6/28/2023 17:49	16.176	0:00:16	06/28/2023	17:48:56	17:49:12		21:15
53	John Doe	06/28/2023 17:49	Lunch	6/28/2023 18:19	1845.763	0:30:46	06/28/2023	17:49:12	18:19:58		21:30
54	John Doe	06/28/2023 18:19	Outbound	6/28/2023 18:20	27.493	0:00:27	06/28/2023	18:19:58	18:20:25		21:45
55	John Doe	06/28/2023 18:20	OnCall	6/28/2023 19:22	3750.91	1:02:31	06/28/2023	18:20:25	19:22:56		22:00
56	John Doe	06/28/2023 19:22	Outbound	6/28/2023 19:24	82.413	0:01:22	06/28/2023	19:22:56	19:24:19		22:15
57	John Doe	06/28/2023 19:24	OnCall	6/28/2023 20:11	2841.887	0:47:22	06/28/2023	19:24:19	20:11:41		22:30
58	John Doe	06/28/2023 20:11	Outbound	6/28/2023 20:13	107.18	0:01:47	06/28/2023	20:11:41	20:13:28		22:45
59	John Doe	06/28/2023 20:13	Break	6/28/2023 20:29	972.431	0:16:12	06/28/2023	20:13:28	20:29:40		23:00
60	John Doe	06/28/2023 20:29	Outbound	6/28/2023 20:29	1.82	0:00:02	06/28/2023	20:29:40	20:29:42		23:15
61	John Doe	06/28/2023 20:29	OnCall	6/28/2023 22:42	7956.801	2:12:37	06/28/2023	20:29:42	22:42:19		23:30
62	John Doe	06/28/2023 22:42	Outbound	6/28/2023 22:42	34.796	0:00:35	06/28/2023	22:42:19	22:42:54		23:45
Sheet1

Cell Formulas
Range	Formula
F3:F62	F3	=E3/86400
G3:G62	G3	=TEXT(B3,"Mm/Dd/Yyyy")
H3:H62	H3	=TEXT(B3,"Hh:mm:ss")
I3:I62	I3	=TEXT(D3,"Hh:mm:ss")

 

[Jeffrey Mahoney]

I took 15 minutes to look at your data. I can't find an easy way to summarize the data into 15 minute time slots using a formula without first dividing your raw data into the proper time slots.