Identify timestamps that occur within 5 minutes of an event.

[cohom]

I searched the forum for similar questions but didn't see anything. Here's the question: We're running TV commercials and want to see leads that came into our website within 5 minutes after a commercial airs.

We have a list of times when the commercials ran and a list of times we received leads. How do I compare them to identify and count all leads timestamps that occurred within 5 minutes of a commercial timestamp?

I'd like something that I can analyze and search all the airings at once. For example, if a commercial aired at 8:25pm, 8:45pm, 9:15pm and 9:28pm (and dozens of others) how do I search all of those times automatically rather than having to manually search for matching times on each one individually. We may end up with 100+ commercials per day.

 

[Comfy]

Here you go:

Excel 2010
	A	B	C	D
2	Lead Time (minutes)	5
3
4
5	Ad Times		Leads	Match
6	03/11/2016 20:25		03/11/2016 20:25	1
7	03/11/2016 20:26		03/11/2016 20:26	2
8	03/11/2016 20:45		03/11/2016 20:27	2
9	03/11/2016 21:15		03/11/2016 20:28	2
10	05/11/2016 19:28		03/11/2016 20:29	2
11			03/11/2016 20:30	2
12			03/11/2016 20:31	1
13			03/11/2016 20:32	0
14			03/11/2016 20:33	0
15			03/11/2016 20:34	0
16			03/11/2016 20:35	0
17			03/11/2016 20:36	0
18			03/11/2016 20:37	0
19			03/11/2016 20:38	0
20			03/11/2016 20:39	0
21			03/11/2016 20:40	0
22			03/11/2016 20:41	0
23			03/11/2016 20:42	0
24			03/11/2016 20:43	0
25			03/11/2016 20:44	0
26			03/11/2016 20:45	1
27			03/11/2016 20:46	1
28			03/11/2016 20:47	1
29			03/11/2016 20:48	1
30			03/11/2016 20:49	1
31			03/11/2016 20:50	1
Sheet1

Cell Formulas
Range	Formula
D6	=SUMPRODUCT((Sheet1!$C6>=Sheet1!$A$6:$A$10)*(Sheet1!$C6<=Sheet1!$A$6:$A$10+$B$2/24/60))

 

[Comfy]

I've left in the counts greater than one as a demo. As I don't know what should happen if you run multiple adds within the specified lead time.

 

[cohom]

Thanks! I'll give this a shot on Monday. I appreciate the help.