Counting a range to see if it encompasses a given date

[Quovardis]

Hi,

Ive been looking all over for a solution for this.

I basically want to count the amount of times a date falls between 2 dates.
The problem is that my start & end date are in different columns of a row and
I have 100's of Rows that have the start and end date

Example of data:

Column 1 Column 2
Row1 10/8/2007 10/11/2007
Row2 10/9/2007 10/12/2007

So in this example I want to count how many times 10/10/2007 will fall on or
between the dates in column1 & column2.

The correct answer would be 2 in this case.

Hope someone can help as im doing my head in with this one.

BR.....Q

 

[Max]

In C1:
=SUMPRODUCT((A1:A10<=--"10-Oct-2007")*(B1:B10>=--"10-Oct-2007"))
where A1:A10 = start dates, B1:B10 = end dates

 

[Quovardis]

Hi Max,

Thanks for the swift reply but unfortunately it doesnt work

9/27/2007 10/5/2007
9/28/2007 10/5/2007
9/26/2007 10/5/2007
9/29/2007 10/5/2007
9/28/2007 10/5/2007
10/3/2007 10/5/2007
10/3/2007 10/5/2007

Basically in my sheet I have here,if im looking for how many times 10/1/2007
will be in the range then it would return 5 as correct answer.

Your formulae for some reason doesnt return this

In C1:

Hi,

[quoted text clipped - 21 lines]

BR.....Q

 

[T. Valko]

Try this:

D1 = 10/1/2007

=SUMPRODUCT(--(D1>=A1:A7),--(D1<=B1:B7))

 

[Max]

It should work fine. Just re-tested it here.

Did you **adjust** the date to be compared in the suggested formula from:
10th Oct 2007 (as per your original post) to 1st Oct 2007 (in your latest
response below), ie did you use:

=SUMPRODUCT((A1:A10<=--"1-Oct-2007")*(B1:B10>=--"1-Oct-2007"))

If the above still doesn't work, that means your source dates data in cols A
and B are not real dates. You can use Data > Text to Columns to convert each
col in turn to real dates. Just select say, col A, click Data > Text to
Columns. Click Next > Next. In step 3, select Date, then choose from the
droplist: MDY, click Finish. Repeat for col B.

 

[Quovardis]

Okay Guys,

Thanks for the help.....it seems to be working....getting some really weird
numbers back so I will double check but its nothing wrong with the formulae.

Thanks again.

Try this:

D1 = 10/1/2007

=SUMPRODUCT(--(D1>=A1:A7),--(D1<=B1:B7))

Hi,

[quoted text clipped - 21 lines]

BR.....Q

 

[Max]

getting some really weird numbers back so I will double check ..

You probably have a mixture of real and text dates in the source cols. As
responsed to you in the other branch, use Data > Text to Columns to convert
it all at one go (per source col) to real dates