Array formula to determine if date/time exists between a number of date/time ranges.

[Chronicide]

Hello everyone,

I've been trying to create an array formula that I can't get to work. I'm new to array formulas, so I think that I'm probably going about it the wrong way.

On one sheet, I have a large number of records. Each record (row) has a category, start-date/time and end-date/time. On another sheet I have a series of records, each with a category and a date/time.

I.e.:

(NOTE: I've used ` to maintain whitespace, which was trimmed)

Sheet 1

Category ```Start DateTime `````End DateTime
CatA ```````2011/01/01 10:00 ```2011/01/01 13:30
CatA ```````2011/01/02 08:00 ```2011/01/02 09:00
CatA ```````2011/01/03 06:00 ```2011/01/03 15:30
CatB ```````2011/01/01 14:00 ```2011/01/01 15:00
CatC ```````2011/01/01 16:00 ```2011/01/01 19:00
CatC ```````2011/01/02 12:00 ```2011/01/02 13:30


Sheet 2


Category ```DateTime
CatA ```````2011/01/01 10:45
CatB ```````2011/01/01 12:30
CatB ```````2011/01/02 15:30
CatC ```````2011/01/02 13:00


I want to add a thrid column to Sheet 2 that returns a 'True' or 'False'. I need the formula to check if the given date/time on sheet 2 exists within any date/time range from sheet 1 of the same category...

So the desired results of the formula would be:


Sheet 2

Category ```DateTime ```````````Exists?
CatA ```````2011/01/01 10:45 ```True
CatB ```````2011/01/01 12:30 ```False
CatB ```````2011/01/02 15:30 ```False
CatC ```````2011/01/02 13:00 ```True

I haven't been able to make a formula that works, let alone returns something meaningful. I would really appreciate any help that you folks could offer. Thanks =)

- Scott

 

[tony80]

If you have Excel 2007 or 2010 then try using COUNTIFS:

=COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,"<=" & B2,Sheet1!C:C,">=" & B2)>0


Otherwise SUMPRODUCT should work for any version

=SUMPRODUCT(--(Sheet1!A1:A9999=A2),--(Sheet1!B1:B9999<=B2),--(Sheet1!C1:C9999>=B2))>0

 

[Chronicide]

Thank you so much! That works perfectly, and better still, I can understand how it works!

You've save me a ton of heartache. Thanks!

- Scott