Calculating the overlap time between observations

[ETSHA]

Hi all,
I'm working on a large dataset (85 K obs), for each observation I have a start date, start time, end date, end time, a field defining whether the observation is a commercial or a show and channel.
I want to calculate the amount of overlap time between observations such that they are both commercials and are different channels. Meaning, if I have three channels, then for a relevant observation (commercial) I want to know the amount of overlap time with each of the two other channels separately.
Any ideas on ow to go about doing this?

ET

 

[Andrew Poulsom]

Welcome to MrExcel.

It may help to see some sample data with the expected results.

 

[ETSHA]

I entered the first 20 rows:

Index	Show/Comm	Channel	S_Date	S_Time	E_Date	E_Time
1	Show	1	1/1/2001	19:0:2	1/1/2001	19:20:59
2	Show	1	1/1/2001	19:21:0	1/1/2001	20:55:18
3	Commercial	1	1/1/2001	20:55:18	1/1/2001	20:59:25
4	Show	1	1/1/2001	20:59:25	1/1/2001	21:0:33
5	Show	1	1/1/2001	21:0:34	1/1/2001	21:31:55
6	Commercial	1	1/1/2001	21:31:55	1/1/2001	21:34:46
7	Show	1	1/1/2001	21:34:46	1/1/2001	22:31:47
8	Commercial	1	1/1/2001	22:31:47	1/1/2001	22:33:55
9	Show	1	1/1/2001	22:33:55	1/1/2001	23:32:35
10	Commercial	2	1/1/2001	19:6:47	1/1/2001	19:10:21
11	Show	2	1/1/2001	19:20:51	1/1/2001	20:0:0
12	Show	2	1/1/2001	20:0:0	1/1/2001	20:33:49
13	Commercial	2	1/1/2001	20:20:15	1/1/2001	20:22:12
14	Commercial	2	1/1/2001	20:33:49	1/1/2001	20:39:12
15	Show	2	1/1/2001	20:39:12	1/1/2001	21:36:13
16	Commercial	2	1/1/2001	20:55:12	1/1/2001	21:0:3
17	Commercial	2	1/1/2001	21:15:21	1/1/2001	21:20:46
18	Commercial	2	1/1/2001	21:36:13	1/1/2001	21:36:20
19	Show	2	1/1/2001	21:36:20	1/1/2001	22:32:51

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[ETSHA]

I entered the first 20 rows:

Index	Show/Comm	Channel	S_Date	S_Time	E_Date	E_Time
1	Show	1	1/1/2001	19:0:2	1/1/2001	19:20:59
2	Show	1	1/1/2001	19:21:0	1/1/2001	20:55:18
3	Commercial	1	1/1/2001	20:55:18	1/1/2001	20:59:25
4	Show	1	1/1/2001	20:59:25	1/1/2001	21:0:33
5	Show	1	1/1/2001	21:0:34	1/1/2001	21:31:55
6	Commercial	1	1/1/2001	21:31:55	1/1/2001	21:34:46
7	Show	1	1/1/2001	21:34:46	1/1/2001	22:31:47
8	Commercial	1	1/1/2001	22:31:47	1/1/2001	22:33:55
9	Show	1	1/1/2001	22:33:55	1/1/2001	23:32:35
10	Commercial	2	1/1/2001	19:6:47	1/1/2001	19:10:21
11	Show	2	1/1/2001	19:20:51	1/1/2001	20:0:0
12	Show	2	1/1/2001	20:0:0	1/1/2001	20:33:49
13	Commercial	2	1/1/2001	20:20:15	1/1/2001	20:22:12
14	Commercial	2	1/1/2001	20:33:49	1/1/2001	20:39:12
15	Show	2	1/1/2001	20:39:12	1/1/2001	21:36:13
16	Commercial	2	1/1/2001	20:55:12	1/1/2001	21:0:3
17	Commercial	2	1/1/2001	21:15:21	1/1/2001	21:20:46
18	Commercial	2	1/1/2001	21:36:13	1/1/2001	21:36:20
19	Show	2	1/1/2001	21:36:20	1/1/2001	22:32:51

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Andrew Poulsom]

And what results do you want?

 

[ETSHA]

say if I have three channels then three more columns - (1) overlap with channel i; (2) overlap with channel j; and (3) overlap with channel k. For an observation of channel i, column (1) will be empty and for the two remaining columns a total time of overlap. The problem I can't figure out is how to make a sort of vlookup with conditions.

ET

 

[Andrew Poulsom]

Post some data that fits that scenario and the expected results.

 

[ETSHA]

From an eye examination we have that Index 53041 and Index 53054 overlap. I would like to make a calculation such that for the row of index 53041 in the column "Overlap with Channel 1" I'l have 0, in the column "Overlap with Channel 2" I'll have empty cause it's the same channel" and in the column "Overlap with Channel 3" I'l have the aount of overlap between the index 53041 and the index 53054.

Index	Show/Comm	Channel	Weekday	S_Date	S_Time	E_Date	E_Time	Duration	Overlap with Channel 1	Overlap with Channel 2	Overlap with Channel 3
53027	Commercial	1	2	26/4/2004	21:7:55	26/4/2004	21:10:36	0002:41
53029	Commercial	1	2	26/4/2004	21:11:58	26/4/2004	21:12:28	0000:30
53031	Commercial	1	2	26/4/2004	21:25:12	26/4/2004	21:26:40	0001:28
53034	Commercial	2	2	26/4/2004	19:29:41	26/4/2004	19:29:48	0000:07
53038	Commercial	2	2	26/4/2004	21:13:52	26/4/2004	21:17:5	0003:13
53039	Commercial	2	2	26/4/2004	21:20:34	26/4/2004	21:20:42	0000:08
53041	Commercial	2	2	26/4/2004	21:32:29	26/4/2004	21:41:40	0009:11
53042	Commercial	2	2	26/4/2004	21:54:48	26/4/2004	22:3:6	0008:18
53043	Commercial	2	2	26/4/2004	22:22:21	26/4/2004	22:30:59	0008:38
53044	Commercial	2	2	26/4/2004	22:44:13	26/4/2004	22:52:24	0008:11
53045	Commercial	2	2	26/4/2004	22:59:41	26/4/2004	23:1:22	0001:41
53046	Commercial	3	2	26/4/2004	19:4:12	26/4/2004	19:4:26	0000:14
53048	Commercial	3	2	26/4/2004	19:29:23	26/4/2004	19:29:40	0000:17
53052	Commercial	3	2	26/4/2004	21:15:21	26/4/2004	21:15:36	0000:15
53054	Commercial	3	2	26/4/2004	21:29:27	26/4/2004	21:34:44	0005:17
53055	Commercial	3	2	26/4/2004	21:49:23	26/4/2004	21:54:32	0005:09
53056	Commercial	3	2	26/4/2004	22:1:16	26/4/2004	22:7:54	0006:38
53058	Commercial	3	2	26/4/2004	22:32:11	26/4/2004	22:38:42	0006:31

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[ETSHA]

Anyone?

 

[ETSHA]

Help please