Remember Me?

 Pe66les Posts: n/a 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

 Biff Posts: n/a Hi!

See this:

http://tinyurl.com/aml9c

Biff

"Pe66les" wrote in message
news 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

 Pe66les Posts: n/a I tried all 3 suggestions, but they didn't work.

"Biff" wrote:

Hi!

See this:

http://tinyurl.com/aml9c

Biff

"Pe66les" wrote in message
news 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

 Biff Posts: n/a 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
news 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

 Pe66les Posts: n/a 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
news 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

 Biff Posts: n/a 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
news 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

 Pe66les Posts: n/a 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
news 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

 Biff Posts: n/a 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
news 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

 Pe66les Posts: n/a 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
news 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

 Biff Posts: n/a 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
news 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post kasey Excel Discussion (Misc queries) 21 July 8th 05 02:03 AM slymeat Excel Worksheet Functions 3 July 5th 05 01:53 AM Rao Ratan Singh Excel Discussion (Misc queries) 2 March 17th 05 02:28 PM PM New Users to Excel 3 January 21st 05 07:47 AM Mark Ada Excel Worksheet Functions 1 December 2nd 04 12:04 AM

All times are GMT +1. The time now is 04:28 PM. Copyright ©2004-2020 ExcelBanter.