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.

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

=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

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.