Counting a range to see if it encompasses a given date
 

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

Counting a range to see if it encompasses a given date
 

In C1:
=SUMPRODUCT((A1:A10<=--"10-Oct-2007")*(B1:B10=--"10-Oct-2007"))
where A1:A10 = start dates, B1:B10 = end dates
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Quovardis" <u38145@uwe wrote in message news:7981a2847c766@uwe...
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

Counting a range to see if it encompasses a given date
 

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

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

[quoted text clipped - 21 lines]

BR.....Q

Counting a range to see if it encompasses a given date
 

Try this:

D1 = 10/1/2007

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

--
Biff
Microsoft Excel MVP


"Quovardis" <u38145@uwe wrote in message news:7981a2847c766@uwe...
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

Counting a range to see if it encompasses a given date
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Quovardis" <u38145@uwe wrote in message news:7981e346487a7@uwe...
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

Counting a range to see if it encompasses a given date
 

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.

T. Valko wrote:
Try this:

D1 = 10/1/2007

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

Hi,

[quoted text clipped - 21 lines]

BR.....Q

Counting a range to see if it encompasses a given date
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Quovardis" <u38145@uwe wrote in message news:79830839af4c6@uwe...
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.