Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 27th 05, 03:05 AM
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  
Old August 27th 05, 03:33 AM
Biff
 
Posts: n/a
Default

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
date 1 as well. Please help me.



  #3   Report Post  
Old August 27th 05, 04:23 AM
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
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
date 1 as well. Please help me.




  #4   Report Post  
Old August 27th 05, 05:20 AM
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
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
date 1 as well. Please help me.






  #5   Report Post  
Old August 27th 05, 05:31 AM
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
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
date 1 as well. Please help me.








  #6   Report Post  
Old August 27th 05, 05:58 AM
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
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
date 1 as well. Please help me.








  #7   Report Post  
Old August 27th 05, 06:36 AM
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
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
date 1 as well. Please help me.









  #8   Report Post  
Old August 27th 05, 07:14 AM
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
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
date 1 as well. Please help me.











  #9   Report Post  
Old August 27th 05, 07:41 AM
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
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
date 1 as well. Please help me.












  #10   Report Post  
Old August 27th 05, 08:19 AM
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
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
date 1 as well. Please help me.
















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 02:28 PM
Automatically enter date and time but only update once. PM New Users to Excel 3 January 21st 05 07:47 AM
Combined date time cell to separate date & time components Mark Ada Excel Worksheet Functions 1 December 2nd 04 12:04 AM


All times are GMT +1. The time now is 04:40 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017