Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Pe66les
 
Posts: n/a
Default How do I calculate if a date is in a certain time frame?

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.
  #2   Report Post  
Biff
 
Posts: n/a
Default

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.



  #3   Report Post  
Pe66les
 
Posts: n/a
Default

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.




  #4   Report Post  
Biff
 
Posts: n/a
Default

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.






  #5   Report Post  
Pe66les
 
Posts: n/a
Default

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.








  #6   Report Post  
Biff
 
Posts: n/a
Default

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.








  #7   Report Post  
Pe66les
 
Posts: n/a
Default

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.









  #8   Report Post  
Biff
 
Posts: n/a
Default

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.











  #9   Report Post  
Pe66les
 
Posts: n/a
Default

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.












  #10   Report Post  
Biff
 
Posts: n/a
Default

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.
















  #11   Report Post  
Pe66les
 
Posts: n/a
Default

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.















  #12   Report Post  
Pe66les
 
Posts: n/a
Default


[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.















  #13   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #14   Report Post  
Pe66les
 
Posts: n/a
Default

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

  #15   Report Post  
Pe66les
 
Posts: n/a
Default

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



  #16   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #17   Report Post  
Pe66les
 
Posts: n/a
Default

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.

  #18   Report Post  
Pe66les
 
Posts: n/a
Default

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.

  #19   Report Post  
Biff
 
Posts: n/a
Default

=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



  #20   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to calculate date and time kasey Excel Discussion (Misc queries) 21 July 8th 05 02:03 AM
calculate anniversary of date after specified date slymeat Excel Worksheet Functions 3 July 5th 05 01:53 AM
Calculate time Rao Ratan Singh Excel Discussion (Misc queries) 2 March 17th 05 03:28 PM
Automatically enter date and time but only update once. PM New Users to Excel 3 January 21st 05 08:47 AM
Combined date time cell to separate date & time components Mark Ada Excel Worksheet Functions 1 December 2nd 04 01:04 AM


All times are GMT +1. The time now is 02:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"