Create a Time Range off Time Stamps, and Count Intersecting values

**athyeh** · 02-19-2014, 03:39 PM

Hi Folks,

I have the following data in two workbooks that mimics as follows:

Workbook A,
Column A (Time Stamps): Column B (Activity):
12:00 PM Basketball
12:30 PM Lunch
1:00 PM Basketball
1:30 PM Baseball
2:30 PM Baseball

Workbook B
Column A (Time Stamps):
12:01 PM
12:03 PM
1:05 PM
1:17 PM

I am looking for code that will take the time stamp from Workbook B (TSB), and IF TSB is within a time range with Activity switching from Basketball to Baseball (ONLY) then Count 1.

Any thoughts? All help is greatly appreciated. Thanks!

**Bernie Deitrick** · 02-19-2014, 07:48 PM

In Workbook B, Sheet1, cell B2, enter this (adjust the 500 and 501 as needed, but keep the one row relative difference)

Formula:

Please Login or Register  to view this content.

and copy down to match your times in column A.

Note that it was ambiguous how to define the "time range with activity switching" so I guessed what you meant.

**athyeh** · 02-21-2014, 05:07 PM

Thank you very much for your reply.

First attempt did not 'work' values returned are all "0" even when I manipulated data to induce a "1". Can you elaborate a little more on what this is doing? I was not expecting Sumproduct and am confused to what intent is. Also, I noticed that while we are placing in Workbook B, the entire function points to B except for the greater than less than statements...