I am tracking dates of service and I need to be able to see if dates fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1 and date 2.
If any fall between these dates then count as 1 or true. If the dates are
before date 1 or after date 2, then I don't want to count them at all. The
problem is if I use date 1 and < date 2 then it counts everything before
date 1 as well. Please help me.

Hi!

See this:

http://tinyurl.com/aml9c

Biff

"Pe66les" wrote in message
...
I am tracking dates of service and I need to be able to see if dates fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1 and date 2.
If any fall between these dates then count as 1 or true. If the dates are
before date 1 or after date 2, then I don't want to count them at all.
The
problem is if I use date 1 and < date 2 then it counts everything before
date 1 as well. Please help me.

I tried all 3 suggestions, but they didn't work.

"Biff" wrote:

Hi!

See this:

http://tinyurl.com/aml9c

Biff

"Pe66les" wrote in message
...
I am tracking dates of service and I need to be able to see if dates fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1 and date 2.
If any fall between these dates then count as 1 or true. If the dates are
before date 1 or after date 2, then I don't want to count them at all.
The
problem is if I use date 1 and < date 2 then it counts everything before
date 1 as well. Please help me.

Are you sure that the dates are true Excel dates?

True Excel dates are really numbers with a special format.

Check the formats for your date cells. If you select a date cell then right
click and choose Format Cells what format does it show as being used?

Biff

"Pe66les" wrote in message
...
I tried all 3 suggestions, but they didn't work.

"Biff" wrote:

Hi!

See this:

http://tinyurl.com/aml9c

Biff

"Pe66les" wrote in message
...
I am tracking dates of service and I need to be able to see if dates
fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1 and date
2.
If any fall between these dates then count as 1 or true. If the dates
are
before date 1 or after date 2, then I don't want to count them at all.
The
problem is if I use date 1 and < date 2 then it counts everything
before
date 1 as well. Please help me.

It is a date cell. It says Type : date

"Biff" wrote:

Are you sure that the dates are true Excel dates?

True Excel dates are really numbers with a special format.

Check the formats for your date cells. If you select a date cell then right
click and choose Format Cells what format does it show as being used?

Biff

"Pe66les" wrote in message
...
I tried all 3 suggestions, but they didn't work.

"Biff" wrote:

Hi!

See this:

http://tinyurl.com/aml9c

Biff

"Pe66les" wrote in message
...
I am tracking dates of service and I need to be able to see if dates
fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1 and date
2.
If any fall between these dates then count as 1 or true. If the dates
are
before date 1 or after date 2, then I don't want to count them at all.
The
problem is if I use date 1 and < date 2 then it counts everything
before
date 1 as well. Please help me.

OK, maybe if you give some examples of what to count and when...

For example....

F1 = 1/1/2005
H1 = 1/2/2005

H5 = 1/1/2005
J5 = 1/10/2005
L5 = blank (empty cell)

What result would you expect from that example? Based on my understanding of
what you want I would think the result should be 1. Also, you didn't really
say whether you actually meant = and <= the dates in F1 and H1. You said
"within" and a literal reading of that would exclude both of the dates in my
example.

Biff

"Pe66les" wrote in message
...
It is a date cell. It says Type : date

"Biff" wrote:

Are you sure that the dates are true Excel dates?

True Excel dates are really numbers with a special format.

Check the formats for your date cells. If you select a date cell then
right
click and choose Format Cells what format does it show as being used?

Biff

"Pe66les" wrote in message
...
I tried all 3 suggestions, but they didn't work.

"Biff" wrote:

Hi!

See this:

http://tinyurl.com/aml9c

Biff

"Pe66les" wrote in message
...
I am tracking dates of service and I need to be able to see if dates
fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1 and
date
2.
If any fall between these dates then count as 1 or true. If the
dates
are
before date 1 or after date 2, then I don't want to count them at
all.
The
problem is if I use date 1 and < date 2 then it counts everything
before
date 1 as well. Please help me.

The dates in F1 and H1 are a 2 month span.
F1 =TODAY()-365 H1 =TODAY()-305.

H5 is 6/17/04
J5 is 1/11/05
L5 is 10/18/04
The only cell which would count is L5. H5 is too long ago, and J5 is not
long enough. so... if I say =$F$1 it counts J5, and if I say <=$H$1 it
counts H5. I need it to count only the dates which fall in between F1 and
H1. I don't know how to combine the equation to make it fit Both criteria at
the same time. How DO you learn all this stuff?


"Biff" wrote:

OK, maybe if you give some examples of what to count and when...

For example....

F1 = 1/1/2005
H1 = 1/2/2005

H5 = 1/1/2005
J5 = 1/10/2005
L5 = blank (empty cell)

What result would you expect from that example? Based on my understanding of
what you want I would think the result should be 1. Also, you didn't really
say whether you actually meant = and <= the dates in F1 and H1. You said
"within" and a literal reading of that would exclude both of the dates in my
example.

Biff

"Pe66les" wrote in message
...
It is a date cell. It says Type : date

"Biff" wrote:

Are you sure that the dates are true Excel dates?

True Excel dates are really numbers with a special format.

Check the formats for your date cells. If you select a date cell then
right
click and choose Format Cells what format does it show as being used?

Biff

"Pe66les" wrote in message
...
I tried all 3 suggestions, but they didn't work.

"Biff" wrote:

Hi!

See this:

http://tinyurl.com/aml9c

Biff

"Pe66les" wrote in message
...
I am tracking dates of service and I need to be able to see if dates
fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1 and
date
2.
If any fall between these dates then count as 1 or true. If the
dates
are
before date 1 or after date 2, then I don't want to count them at
all.
The
problem is if I use date 1 and < date 2 then it counts everything
before
date 1 as well. Please help me.

Hi!

The only cell which would count is L5. H5 is too long ago, and J5 is not
long enough. so... if I say =$F$1 it counts J5, and if I say <=$H$1 it
counts H5.

OK, that's where you're not understanding how the formula works. The dates
have to meet both of those conditions to be counted. The date has to be =F1
AND <=H1.

F1 = 8/27/2004
H1 = 11/26/2004

H5 = 6/17/2004 is NOT F1 but IS <H1

So this is how it gets evaluated:

FALSE * TRUE = 0 (not counted)

And the formula does the same thing for the other dates so that you have an
array that looks like this:

H5 = 6/17/2004 = FALSE * TRUE = 0
J5 = 1/11/2005 = TRUE * FALSE = 0
L5 = 10/18/2004 = TRUE * TRUE = 1

So, the only date counted is in L5.

This formula is a little more robust in that it accounts for empty cells:

=IF(COUNT(H5,J5,L5)0,IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5=F1),--(H5:L5<=H1)),1,0),0)

Biff

"Pe66les" wrote in message
...
The dates in F1 and H1 are a 2 month span.
F1 =TODAY()-365 H1 =TODAY()-305.

H5 is 6/17/04
J5 is 1/11/05
L5 is 10/18/04
The only cell which would count is L5. H5 is too long ago, and J5 is not
long enough. so... if I say =$F$1 it counts J5, and if I say <=$H$1 it
counts H5. I need it to count only the dates which fall in between F1 and
H1. I don't know how to combine the equation to make it fit Both criteria
at
the same time. How DO you learn all this stuff?


"Biff" wrote:

OK, maybe if you give some examples of what to count and when...

For example....

F1 = 1/1/2005
H1 = 1/2/2005

H5 = 1/1/2005
J5 = 1/10/2005
L5 = blank (empty cell)

What result would you expect from that example? Based on my understanding
of
what you want I would think the result should be 1. Also, you didn't
really
say whether you actually meant = and <= the dates in F1 and H1. You said
"within" and a literal reading of that would exclude both of the dates in
my
example.

Biff

"Pe66les" wrote in message
...
It is a date cell. It says Type : date

"Biff" wrote:

Are you sure that the dates are true Excel dates?

True Excel dates are really numbers with a special format.

Check the formats for your date cells. If you select a date cell then
right
click and choose Format Cells what format does it show as being used?

Biff

"Pe66les" wrote in message
...
I tried all 3 suggestions, but they didn't work.

"Biff" wrote:

Hi!

See this:

http://tinyurl.com/aml9c

Biff

"Pe66les" wrote in message
...
I am tracking dates of service and I need to be able to see if
dates
fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1 and
date
2.
If any fall between these dates then count as 1 or true. If the
dates
are
before date 1 or after date 2, then I don't want to count them at
all.
The
problem is if I use date 1 and < date 2 then it counts
everything
before
date 1 as well. Please help me.

Biff, it still counts dates prior to F1.

"Biff" wrote:

Hi!

The only cell which would count is L5. H5 is too long ago, and J5 is not
long enough. so... if I say =$F$1 it counts J5, and if I say <=$H$1 it
counts H5.

OK, that's where you're not understanding how the formula works. The dates
have to meet both of those conditions to be counted. The date has to be =F1
AND <=H1.

F1 = 8/27/2004
H1 = 11/26/2004

H5 = 6/17/2004 is NOT F1 but IS <H1

So this is how it gets evaluated:

FALSE * TRUE = 0 (not counted)

And the formula does the same thing for the other dates so that you have an
array that looks like this:

H5 = 6/17/2004 = FALSE * TRUE = 0
J5 = 1/11/2005 = TRUE * FALSE = 0
L5 = 10/18/2004 = TRUE * TRUE = 1

So, the only date counted is in L5.

This formula is a little more robust in that it accounts for empty cells:

=IF(COUNT(H5,J5,L5)0,IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5=F1),--(H5:L5<=H1)),1,0),0)

Biff

"Pe66les" wrote in message
...
The dates in F1 and H1 are a 2 month span.
F1 =TODAY()-365 H1 =TODAY()-305.

H5 is 6/17/04
J5 is 1/11/05
L5 is 10/18/04
The only cell which would count is L5. H5 is too long ago, and J5 is not
long enough. so... if I say =$F$1 it counts J5, and if I say <=$H$1 it
counts H5. I need it to count only the dates which fall in between F1 and
H1. I don't know how to combine the equation to make it fit Both criteria
at
the same time. How DO you learn all this stuff?


"Biff" wrote:

OK, maybe if you give some examples of what to count and when...

For example....

F1 = 1/1/2005
H1 = 1/2/2005

H5 = 1/1/2005
J5 = 1/10/2005
L5 = blank (empty cell)

What result would you expect from that example? Based on my understanding
of
what you want I would think the result should be 1. Also, you didn't
really
say whether you actually meant = and <= the dates in F1 and H1. You said
"within" and a literal reading of that would exclude both of the dates in
my
example.

Biff

"Pe66les" wrote in message
...
It is a date cell. It says Type : date

"Biff" wrote:

Are you sure that the dates are true Excel dates?

True Excel dates are really numbers with a special format.

Check the formats for your date cells. If you select a date cell then
right
click and choose Format Cells what format does it show as being used?

Biff

"Pe66les" wrote in message
...
I tried all 3 suggestions, but they didn't work.

"Biff" wrote:

Hi!

See this:

http://tinyurl.com/aml9c

Biff

"Pe66les" wrote in message
...
I am tracking dates of service and I need to be able to see if
dates
fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1 and
date
2.
If any fall between these dates then count as 1 or true. If the
dates
are
before date 1 or after date 2, then I don't want to count them at
all.
The
problem is if I use date 1 and < date 2 then it counts
everything
before
date 1 as well. Please help me.

Can you send me a copy of the file? If so, let me know how to contact you.

Biff

"Pe66les" wrote in message
...
Biff, it still counts dates prior to F1.

"Biff" wrote:

Hi!

The only cell which would count is L5. H5 is too long ago, and J5 is
not
long enough. so... if I say =$F$1 it counts J5, and if I say <=$H$1 it
counts H5.

OK, that's where you're not understanding how the formula works. The
dates
have to meet both of those conditions to be counted. The date has to be
=F1
AND <=H1.

F1 = 8/27/2004
H1 = 11/26/2004

H5 = 6/17/2004 is NOT F1 but IS <H1

So this is how it gets evaluated:

FALSE * TRUE = 0 (not counted)

And the formula does the same thing for the other dates so that you have
an
array that looks like this:

H5 = 6/17/2004 = FALSE * TRUE = 0
J5 = 1/11/2005 = TRUE * FALSE = 0
L5 = 10/18/2004 = TRUE * TRUE = 1

So, the only date counted is in L5.

This formula is a little more robust in that it accounts for empty cells:

=IF(COUNT(H5,J5,L5)0,IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5=F1),--(H5:L5<=H1)),1,0),0)

Biff

"Pe66les" wrote in message
...
The dates in F1 and H1 are a 2 month span.
F1 =TODAY()-365 H1 =TODAY()-305.

H5 is 6/17/04
J5 is 1/11/05
L5 is 10/18/04
The only cell which would count is L5. H5 is too long ago, and J5 is
not
long enough. so... if I say =$F$1 it counts J5, and if I say <=$H$1
it
counts H5. I need it to count only the dates which fall in between F1
and
H1. I don't know how to combine the equation to make it fit Both
criteria
at
the same time. How DO you learn all this stuff?


"Biff" wrote:

OK, maybe if you give some examples of what to count and when...

For example....

F1 = 1/1/2005
H1 = 1/2/2005

H5 = 1/1/2005
J5 = 1/10/2005
L5 = blank (empty cell)

What result would you expect from that example? Based on my
understanding
of
what you want I would think the result should be 1. Also, you didn't
really
say whether you actually meant = and <= the dates in F1 and H1. You
said
"within" and a literal reading of that would exclude both of the dates
in
my
example.

Biff

"Pe66les" wrote in message
...
It is a date cell. It says Type : date

"Biff" wrote:

Are you sure that the dates are true Excel dates?

True Excel dates are really numbers with a special format.

Check the formats for your date cells. If you select a date cell
then
right
click and choose Format Cells what format does it show as being
used?

Biff

"Pe66les" wrote in message
...
I tried all 3 suggestions, but they didn't work.

"Biff" wrote:

Hi!

See this:

http://tinyurl.com/aml9c

Biff

"Pe66les" wrote in message
...
I am tracking dates of service and I need to be able to see if
dates
fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1
and
date
2.
If any fall between these dates then count as 1 or true. If
the
dates
are
before date 1 or after date 2, then I don't want to count them
at
all.
The
problem is if I use date 1 and < date 2 then it counts
everything
before
date 1 as well. Please help me.

e-mail - see my profile

"Biff" wrote:

Can you send me a copy of the file? If so, let me know how to contact you.

Biff

"Pe66les" wrote in message
...
Biff, it still counts dates prior to F1.

"Biff" wrote:

Hi!

The only cell which would count is L5. H5 is too long ago, and J5 is
not
long enough. so... if I say =$F$1 it counts J5, and if I say <=$H$1 it
counts H5.

OK, that's where you're not understanding how the formula works. The
dates
have to meet both of those conditions to be counted. The date has to be
=F1
AND <=H1.

F1 = 8/27/2004
H1 = 11/26/2004

H5 = 6/17/2004 is NOT F1 but IS <H1

So this is how it gets evaluated:

FALSE * TRUE = 0 (not counted)

And the formula does the same thing for the other dates so that you have
an
array that looks like this:

H5 = 6/17/2004 = FALSE * TRUE = 0
J5 = 1/11/2005 = TRUE * FALSE = 0
L5 = 10/18/2004 = TRUE * TRUE = 1

So, the only date counted is in L5.

This formula is a little more robust in that it accounts for empty cells:

=IF(COUNT(H5,J5,L5)0,IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5=F1),--(H5:L5<=H1)),1,0),0)

Biff

"Pe66les" wrote in message
...
The dates in F1 and H1 are a 2 month span.
F1 =TODAY()-365 H1 =TODAY()-305.

H5 is 6/17/04
J5 is 1/11/05
L5 is 10/18/04
The only cell which would count is L5. H5 is too long ago, and J5 is
not
long enough. so... if I say =$F$1 it counts J5, and if I say <=$H$1
it
counts H5. I need it to count only the dates which fall in between F1
and
H1. I don't know how to combine the equation to make it fit Both
criteria
at
the same time. How DO you learn all this stuff?


"Biff" wrote:

OK, maybe if you give some examples of what to count and when...

For example....

F1 = 1/1/2005
H1 = 1/2/2005

H5 = 1/1/2005
J5 = 1/10/2005
L5 = blank (empty cell)

What result would you expect from that example? Based on my
understanding
of
what you want I would think the result should be 1. Also, you didn't
really
say whether you actually meant = and <= the dates in F1 and H1. You
said
"within" and a literal reading of that would exclude both of the dates
in
my
example.

Biff

"Pe66les" wrote in message
...
It is a date cell. It says Type : date

"Biff" wrote:

Are you sure that the dates are true Excel dates?

True Excel dates are really numbers with a special format.

Check the formats for your date cells. If you select a date cell
then
right
click and choose Format Cells what format does it show as being
used?

Biff

"Pe66les" wrote in message
...
I tried all 3 suggestions, but they didn't work.

"Biff" wrote:

Hi!

See this:

http://tinyurl.com/aml9c

Biff

"Pe66les" wrote in message
...
I am tracking dates of service and I need to be able to see if
dates
fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1
and
date
2.
If any fall between these dates then count as 1 or true. If
the
dates
are
before date 1 or after date 2, then I don't want to count them
at
all.
The
problem is if I use date 1 and < date 2 then it counts
everything
before
date 1 as well. Please help me.

[display


Can you send me a copy of the file? If so, let me know how to contact you.

Biff

"Pe66les" wrote in message
...
Biff, it still counts dates prior to F1.

"Biff" wrote:

Hi!

The only cell which would count is L5. H5 is too long ago, and J5 is
not
long enough. so... if I say =$F$1 it counts J5, and if I say <=$H$1 it
counts H5.

OK, that's where you're not understanding how the formula works. The
dates
have to meet both of those conditions to be counted. The date has to be
=F1
AND <=H1.

F1 = 8/27/2004
H1 = 11/26/2004

H5 = 6/17/2004 is NOT F1 but IS <H1

So this is how it gets evaluated:

FALSE * TRUE = 0 (not counted)

And the formula does the same thing for the other dates so that you have
an
array that looks like this:

H5 = 6/17/2004 = FALSE * TRUE = 0
J5 = 1/11/2005 = TRUE * FALSE = 0
L5 = 10/18/2004 = TRUE * TRUE = 1

So, the only date counted is in L5.

This formula is a little more robust in that it accounts for empty cells:

=IF(COUNT(H5,J5,L5)0,IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5=F1),--(H5:L5<=H1)),1,0),0)

Biff

"Pe66les" wrote in message
...
The dates in F1 and H1 are a 2 month span.
F1 =TODAY()-365 H1 =TODAY()-305.

H5 is 6/17/04
J5 is 1/11/05
L5 is 10/18/04
The only cell which would count is L5. H5 is too long ago, and J5 is
not
long enough. so... if I say =$F$1 it counts J5, and if I say <=$H$1
it
counts H5. I need it to count only the dates which fall in between F1
and
H1. I don't know how to combine the equation to make it fit Both
criteria
at
the same time. How DO you learn all this stuff?


"Biff" wrote:

OK, maybe if you give some examples of what to count and when...

For example....

F1 = 1/1/2005
H1 = 1/2/2005

H5 = 1/1/2005
J5 = 1/10/2005
L5 = blank (empty cell)

What result would you expect from that example? Based on my
understanding
of
what you want I would think the result should be 1. Also, you didn't
really
say whether you actually meant = and <= the dates in F1 and H1. You
said
"within" and a literal reading of that would exclude both of the dates
in
my
example.

Biff

"Pe66les" wrote in message
...
It is a date cell. It says Type : date

"Biff" wrote:

Are you sure that the dates are true Excel dates?

True Excel dates are really numbers with a special format.

Check the formats for your date cells. If you select a date cell
then
right
click and choose Format Cells what format does it show as being
used?

Biff

"Pe66les" wrote in message
...
I tried all 3 suggestions, but they didn't work.

"Biff" wrote:

Hi!

See this:

http://tinyurl.com/aml9c

Biff

"Pe66les" wrote in message
...
I am tracking dates of service and I need to be able to see if
dates
fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1
and
date
2.
If any fall between these dates then count as 1 or true. If
the
dates
are
before date 1 or after date 2, then I don't want to count them
at
all.
The
problem is if I use date 1 and < date 2 then it counts
everything
before
date 1 as well. Please help me.

On Fri, 26 Aug 2005 19:05:04 -0700, "Pe66les"
wrote:

I am tracking dates of service and I need to be able to see if dates fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1 and date 2.
If any fall between these dates then count as 1 or true. If the dates are
before date 1 or after date 2, then I don't want to count them at all. The
problem is if I use date 1 and < date 2 then it counts everything before
date 1 as well. Please help me.


With the range of dates that you are checking named "rng" (you can substitute a
cell reference for this):

=MIN(1,COUNTIF(rng,"=" & date1) - COUNTIF(rng,"" & date2))

date1 and date2 may be cell references also.


--ron

With the range of dates that you are checking named "rng" ----How do I do this?

"Ron Rosenfeld" wrote:

On Fri, 26 Aug 2005 19:05:04 -0700, "Pe66les"
wrote:

I am tracking dates of service and I need to be able to see if dates fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1 and date 2.
If any fall between these dates then count as 1 or true. If the dates are
before date 1 or after date 2, then I don't want to count them at all. The
problem is if I use date 1 and < date 2 then it counts everything before
date 1 as well. Please help me.


With the range of dates that you are checking named "rng" (you can substitute a
cell reference for this):

=MIN(1,COUNTIF(rng,"=" & date1) - COUNTIF(rng,"" & date2))

date1 and date2 may be cell references also.


--ron

I tried your suggestion but it is still counting 1 when it should be 0,
This is what I typed:
=MIN(1,COUNTIF(H5:L5,"=" &$F$1) - COUNTIF(H5:L5,"<=" &$H$1))
What am I doing wrong?


"Ron Rosenfeld" wrote:

On Fri, 26 Aug 2005 19:05:04 -0700, "Pe66les"
wrote:

I am tracking dates of service and I need to be able to see if dates fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1 and date 2.
If any fall between these dates then count as 1 or true. If the dates are
before date 1 or after date 2, then I don't want to count them at all. The
problem is if I use date 1 and < date 2 then it counts everything before
date 1 as well. Please help me.


With the range of dates that you are checking named "rng" (you can substitute a
cell reference for this):

=MIN(1,COUNTIF(rng,"=" & date1) - COUNTIF(rng,"" & date2))

date1 and date2 may be cell references also.


--ron

On Sat, 27 Aug 2005 01:33:01 -0700, "Pe66les"
wrote:

I tried your suggestion but it is still counting 1 when it should be 0,
This is what I typed:
=MIN(1,COUNTIF(H5:L5,"=" &$F$1) - COUNTIF(H5:L5,"<=" &$H$1))


What am I doing wrong?


Uh, you are NOT using the equation I posted. You have the comparison operator
incorrect in your second COUNTIF function.

Assuming F1 contains your earlier date, and H1 contains your later date.

Change yours to:

=MIN(1,COUNTIF(H5:L5,"=" &$F$1) - COUNTIF(H5:L5,"" &$H$1))


--ron

WOOOHOOOO!!!!! I did it!!!! I used:
=3-COUNTIF(H5:L5,"="&$H$1)-COUNTIF(H5:L5,"<="&$F$1)
It worked!!!!! Thank You so much for helping me figure out what I needed
to do!!!

"Pe66les" wrote:

I am tracking dates of service and I need to be able to see if dates fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1 and date 2.
If any fall between these dates then count as 1 or true. If the dates are
before date 1 or after date 2, then I don't want to count them at all. The
problem is if I use date 1 and < date 2 then it counts everything before
date 1 as well. Please help me.

OK, You get A Double WOOOOOHOOOO!!!!! I corrected my mistake and your
equation is much better than mine. Thank you!!!! Thank you!!! Thank you!!!

"Pe66les" wrote:

WOOOHOOOO!!!!! I did it!!!! I used:
=3-COUNTIF(H5:L5,"="&$H$1)-COUNTIF(H5:L5,"<="&$F$1)
It worked!!!!! Thank You so much for helping me figure out what I needed
to do!!!

"Pe66les" wrote:

I am tracking dates of service and I need to be able to see if dates fall
within a certain time frame.
column F Column H
row 1 (date 1) (date 2)


column H column J column L
(Client 1) date date date

I want to see if any of the clients dates fall between date 1 and date 2.
If any fall between these dates then count as 1 or true. If the dates are
before date 1 or after date 2, then I don't want to count them at all. The
problem is if I use date 1 and < date 2 then it counts everything before
date 1 as well. Please help me.

=MIN(1,COUNTIF(H5:L5,"=" &$F$1) - COUNTIF(H5:L5,"" &$H$1))

Hmmm...

If that works then so should:

=IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5=F1),--(H5:L5<=H1)),1,0)

I was accounting for the non-contiguous range and the possibilty of other
numeric data within the range, but even if you leave that out:

=IF(SUMPRODUCT(--(H5:L5=F1),--(H5:L5<=H1)),1,0)

That should also work.

I don't get it!

Biff

"Ron Rosenfeld" wrote in message
...
On Sat, 27 Aug 2005 01:33:01 -0700, "Pe66les"
wrote:

I tried your suggestion but it is still counting 1 when it should be 0,
This is what I typed:
=MIN(1,COUNTIF(H5:L5,"=" &$F$1) - COUNTIF(H5:L5,"<=" &$H$1))


What am I doing wrong?


Uh, you are NOT using the equation I posted. You have the comparison
operator
incorrect in your second COUNTIF function.

Assuming F1 contains your earlier date, and H1 contains your later date.

Change yours to:

=MIN(1,COUNTIF(H5:L5,"=" &$F$1) - COUNTIF(H5:L5,"" &$H$1))


--ron

On Sat, 27 Aug 2005 14:48:41 -0400, "Biff" wrote:

=MIN(1,COUNTIF(H5:L5,"=" &$F$1) - COUNTIF(H5:L5,"" &$H$1))

Hmmm...

If that works then so should:

=IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5=F1),--(H5:L5<=H1)),1,0)

I was accounting for the non-contiguous range and the possibilty of other
numeric data within the range, but even if you leave that out:

=IF(SUMPRODUCT(--(H5:L5=F1),--(H5:L5<=H1)),1,0)

That should also work.

I don't get it!

When Pe66les posted the variation of *my* formula that he actually tried, it
was incorrect. Perhaps the same thing was going on with the translation of
yours.


--ron