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]

Please be patient. Bumping so frequently makes you appear demanding. Your question isn't trivial so a solution may take some time, if one exists at all.

 

[Andrew Poulsom]

Formula in J2 copied down and across. I formatted the cells as hh:mm:ss;;; to suppress the display of zeroes. The formula is complex, so I don't know if it will work for 85,000 rows.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	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
2	53027	Commercial	1	2	26/04/2004	21:07:55	26/04/2004	21:10:36	0002:41
3	53029	Commercial	1	2	26/04/2004	21:11:58	26/04/2004	21:12:28	0000:30
4	53031	Commercial	1	2	26/04/2004	21:25:12	26/04/2004	21:26:40	0001:28
5	53034	Commercial	2	2	26/04/2004	19:29:41	26/04/2004	19:29:48	0000:07
6	53038	Commercial	2	2	26/04/2004	21:13:52	26/04/2004	21:17:05	0003:13			00:00:15
7	53039	Commercial	2	2	26/04/2004	21:20:34	26/04/2004	21:20:42	0000:08
8	53041	Commercial	2	2	26/04/2004	21:32:29	26/04/2004	21:41:40	0009:11			00:02:15
9	53042	Commercial	2	2	26/04/2004	21:54:48	26/04/2004	22:03:06	0008:18			00:01:50
10	53043	Commercial	2	2	26/04/2004	22:22:21	26/04/2004	22:30:59	0008:38
11	53044	Commercial	2	2	26/04/2004	22:44:13	26/04/2004	22:52:24	0008:11
12	53045	Commercial	2	2	26/04/2004	22:59:41	26/04/2004	23:01:22	0001:41
13	53046	Commercial	3	2	26/04/2004	19:04:12	26/04/2004	19:04:26	0000:14
14	53048	Commercial	3	2	26/04/2004	19:29:23	26/04/2004	19:29:40	0000:17
15	53052	Commercial	3	2	26/04/2004	21:15:21	26/04/2004	21:15:36	0000:15		00:00:15
16	53054	Commercial	3	2	26/04/2004	21:29:27	26/04/2004	21:34:44	0005:17		00:02:15
17	53055	Commercial	3	2	26/04/2004	21:49:23	26/04/2004	21:54:32	0005:09
18	53056	Commercial	3	2	26/04/2004	22:01:16	26/04/2004	22:07:54	0006:38		00:01:50
19	53058	Commercial	3	2	26/04/2004	22:32:11	26/04/2004	22:38:42	0006:31
Sheet1

Cell Formulas
Range	Formula
J2	{=IF($C2=(RIGHT(J$1,1)+0),"",SUM(IF($C$2:$C$19=(RIGHT(J$1,1)+0),IF((($E$2:$E$19+$F$2:$F$19)<=($E2+$F2))*(($G$2:$G$19+$H$2:$H$19)>=($E2+$F2)),IF(($G$2:$G$19+$H$2:$H$19)>=($G2+$H2),($G2+$H2),($G$2:$G$19+$H$2:$H$19))-IF(($E$2:$E$19+$F$2:$F$19)<=($E2+$F2),($E2+$F2),($E$2:$E$19+$F$2:$F$19)),IF((($E$2:$E$19+$F$2:$F$19)>=($E2+$F2))*(($E$2:$E$19+$F$2:$F$19)<=($G2+$H2)),IF(($G$2:$G$19+$H$2:$H$19)>=($G2+$H2),($G2+$H2),($G$2:$G$19+$H$2:$H$19))-IF(($E$2:$E$19+$F$2:$F$19)<=($E2+$F2),($E2+$F2),($E$2:$E$19+$F$2:$F$19)))))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[shg]

That is impressive, Andrew!

 

[ETSHA]

Looks great, thanks a lot, I'll check it out.

 

[barry houdini]

I think you can simplify Andrew's formula a little - this version in J2 should give the same results:

=IF($C2=RIGHT(J$1)+0,"",SUM(IF(($C$2:$C$19=RIGHT(J$1)+0)*($E$2:$E$19+$F$2:$F$19<$G2+$H2)*($G$2:$G$19+$H$2:$H$19>$E2+$F2),IF($G$2:$G$19+$H$2:$H$19< $G2+$H2,$G$2:$G$19+$H$2:$H$19,$G2+$H2)-IF($E$2:$E$19+$F$2:$F$19 >$E2+$F2,$E$2:$E$19+$F$2:$F$19,$E2+$F2))))

confirmed with CTRL+SHIFT+ENTER