How do I calculate if a date is in a certain time frame?

P

Pe66les

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

Biff

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
 
P

Pe66les

It is a date cell. It says Type : date

Biff said:
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
Click to expand...
 
B

Biff

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
 
P

Pe66les

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?
 
B

Biff

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.
Click to expand...

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
 
P

Pe66les

Biff, it still counts dates prior to F1.

Biff said:
Hi!


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
Click to expand...
 
R

Ron Rosenfeld

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.
Click to expand...


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
 
P

Pe66les

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?
 
R

Ron Rosenfeld

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?
Click to expand...

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
 
P

Pe66les

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!!!
 
P

Pe66les

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

Biff

=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
 
R

Ron Rosenfeld

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!
Click to expand...

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
 
Top