Compare/Find Exact Date match with varying time
 

I have a column that is formatted with a date and timestamp
10/4/2007 14:21

I want to find all the files that were completed on a specific date at
varying times.
I searched the help and found a little on criteria using wildcards, but I
can't seem to figure out the correct way to write my formula.

I tried the following and keep getting a result of 0 (manual calculation
proves the correct answer is 0):

$H2:H1000="9/2/2007*" - 0
$H2:H1000="9/2/2007"* - error
$H2:H1000=9/2/2007* - error

Perhaps my whole formula is incorrect somehow. I've got multiple criteria,
one being matching a separate columns value to an array (thank you to the
answer-people on my previous posts for making this possible for me).

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))

Compare/Find Exact Date match with varying time
 

I got a little closer using COUNT:

=COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*"))

But that yielded a false answer as well. I got 2 when hitting <ENTER and
1998 if I did the CSE (ctrl / shift / enter)

"sweens319" wrote:

I have a column that is formatted with a date and timestamp
10/4/2007 14:21

I want to find all the files that were completed on a specific date at
varying times.
I searched the help and found a little on criteria using wildcards, but I
can't seem to figure out the correct way to write my formula.

I tried the following and keep getting a result of 0 (manual calculation
proves the correct answer is 0):

$H2:H1000="9/2/2007*" - 0
$H2:H1000="9/2/2007"* - error
$H2:H1000=9/2/2007* - error

Perhaps my whole formula is incorrect somehow. I've got multiple criteria,
one being matching a separate columns value to an array (thank you to the
answer-people on my previous posts for making this possible for me).

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))

Compare/Find Exact Date match with varying time
 

Try this:

=SUMPRODUCT(--(CSBD!K2:K1000=IDX),--(INT(CSBD!$H2:H1000)=--"9/2/2007"))


"sweens319" wrote:

I have a column that is formatted with a date and timestamp
10/4/2007 14:21

I want to find all the files that were completed on a specific date at
varying times.
I searched the help and found a little on criteria using wildcards, but I
can't seem to figure out the correct way to write my formula.

I tried the following and keep getting a result of 0 (manual calculation
proves the correct answer is 0):

$H2:H1000="9/2/2007*" - 0
$H2:H1000="9/2/2007"* - error
$H2:H1000=9/2/2007* - error

Perhaps my whole formula is incorrect somehow. I've got multiple criteria,
one being matching a separate columns value to an array (thank you to the
answer-people on my previous posts for making this possible for me).

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))

Compare/Find Exact Date match with varying time
 

Getting:

#N/A

using that one.

"Teethless mama" wrote:

Try this:

=SUMPRODUCT(--(CSBD!K2:K1000=IDX),--(INT(CSBD!$H2:H1000)=--"9/2/2007"))


"sweens319" wrote:

I have a column that is formatted with a date and timestamp
10/4/2007 14:21

I want to find all the files that were completed on a specific date at
varying times.
I searched the help and found a little on criteria using wildcards, but I
can't seem to figure out the correct way to write my formula.

I tried the following and keep getting a result of 0 (manual calculation
proves the correct answer is 0):

$H2:H1000="9/2/2007*" - 0
$H2:H1000="9/2/2007"* - error
$H2:H1000=9/2/2007* - error

Perhaps my whole formula is incorrect somehow. I've got multiple criteria,
one being matching a separate columns value to an array (thank you to the
answer-people on my previous posts for making this possible for me).

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))

Compare/Find Exact Date match with varying time
 

Assuming that your cells are not storing the dates and times as text
strings, but as Excel date & time values, then you'll need to convert and
process the data.

One option for your condition might be along the lines of either
INT(cell_ref)=DATEVALUE("9/4/2007") or better (to avoid ambiguities of date
representation) INT(cell_ref)=DATE(2007,4,9)
Another option might be something like TEXT(cell_ref,"d/m/yyyy")="9/4/2007"
--
David Biddulph

"sweens319" wrote in message
...
I got a little closer using COUNT:

=COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*"))

But that yielded a false answer as well. I got 2 when hitting <ENTER and
1998 if I did the CSE (ctrl / shift / enter)

"sweens319" wrote:

I have a column that is formatted with a date and timestamp
10/4/2007 14:21

I want to find all the files that were completed on a specific date at
varying times.
I searched the help and found a little on criteria using wildcards, but I
can't seem to figure out the correct way to write my formula.

I tried the following and keep getting a result of 0 (manual calculation
proves the correct answer is 0):

$H2:H1000="9/2/2007*" - 0
$H2:H1000="9/2/2007"* - error
$H2:H1000=9/2/2007* - error

Perhaps my whole formula is incorrect somehow. I've got multiple
criteria,
one being matching a separate columns value to an array (thank you to the
answer-people on my previous posts for making this possible for me).

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))

Compare/Find Exact Date match with varying time
 

Put quotes around the "IDX"

=SUMPRODUCT(--(CSBD!K2:K1000="IDX"),--(INT(CSBD!$H2:H1000)=--"9/2/2007"))


"sweens319" wrote:

Getting:

#N/A

using that one.

"Teethless mama" wrote:

Try this:

=SUMPRODUCT(--(CSBD!K2:K1000=IDX),--(INT(CSBD!$H2:H1000)=--"9/2/2007"))


"sweens319" wrote:

I have a column that is formatted with a date and timestamp
10/4/2007 14:21

I want to find all the files that were completed on a specific date at
varying times.
I searched the help and found a little on criteria using wildcards, but I
can't seem to figure out the correct way to write my formula.

I tried the following and keep getting a result of 0 (manual calculation
proves the correct answer is 0):

$H2:H1000="9/2/2007*" - 0
$H2:H1000="9/2/2007"* - error
$H2:H1000=9/2/2007* - error

Perhaps my whole formula is incorrect somehow. I've got multiple criteria,
one being matching a separate columns value to an array (thank you to the
answer-people on my previous posts for making this possible for me).

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))

Compare/Find Exact Date match with varying time
 

I'm not really sure how it's stored. The cells are formatted as "Custom"

m/d/yyy h:mm

Perhaps I am just dumb, but I can't seem to figure out how to incorporate
your suggestions (DATEVALUE, DATE, INT, TEXT, etc) into my formula.

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))

or

=COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*"))


"David Biddulph" wrote:

Assuming that your cells are not storing the dates and times as text
strings, but as Excel date & time values, then you'll need to convert and
process the data.

One option for your condition might be along the lines of either
INT(cell_ref)=DATEVALUE("9/4/2007") or better (to avoid ambiguities of date
representation) INT(cell_ref)=DATE(2007,4,9)
Another option might be something like TEXT(cell_ref,"d/m/yyyy")="9/4/2007"
--
David Biddulph

"sweens319" wrote in message
...
I got a little closer using COUNT:

=COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*"))

But that yielded a false answer as well. I got 2 when hitting <ENTER and
1998 if I did the CSE (ctrl / shift / enter)

"sweens319" wrote:

I have a column that is formatted with a date and timestamp
10/4/2007 14:21

I want to find all the files that were completed on a specific date at
varying times.
I searched the help and found a little on criteria using wildcards, but I
can't seem to figure out the correct way to write my formula.

I tried the following and keep getting a result of 0 (manual calculation
proves the correct answer is 0):

$H2:H1000="9/2/2007*" - 0
$H2:H1000="9/2/2007"* - error
$H2:H1000=9/2/2007* - error

Perhaps my whole formula is incorrect somehow. I've got multiple
criteria,
one being matching a separate columns value to an array (thank you to the
answer-people on my previous posts for making this possible for me).

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))

Compare/Find Exact Date match with varying time
 

Could the answer be a combination of both of these attempts (David &
Teethless)?

I don't think putting quotes around IDX is correct, because IDX is a named
array. But, then again, I could be wrong, because without the quotes I get
#N/A for the result, and with the quotes I get $0 (14 is the correct answer).

Thank you for your continued help.

"Teethless mama" wrote:

Put quotes around the "IDX"

=SUMPRODUCT(--(CSBD!K2:K1000="IDX"),--(INT(CSBD!$H2:H1000)=--"9/2/2007"))


"sweens319" wrote:

Getting:

#N/A

using that one.

"Teethless mama" wrote:

Try this:

=SUMPRODUCT(--(CSBD!K2:K1000=IDX),--(INT(CSBD!$H2:H1000)=--"9/2/2007"))


"sweens319" wrote:

I have a column that is formatted with a date and timestamp
10/4/2007 14:21

I want to find all the files that were completed on a specific date at
varying times.
I searched the help and found a little on criteria using wildcards, but I
can't seem to figure out the correct way to write my formula.

I tried the following and keep getting a result of 0 (manual calculation
proves the correct answer is 0):

$H2:H1000="9/2/2007*" - 0
$H2:H1000="9/2/2007"* - error
$H2:H1000=9/2/2007* - error

Perhaps my whole formula is incorrect somehow. I've got multiple criteria,
one being matching a separate columns value to an array (thank you to the
answer-people on my previous posts for making this possible for me).

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))

Compare/Find Exact Date match with varying time
 

To get all values with the same date use something like

=SUMPRODUCT(--(INT(CSBD!H2:H1000)=DATE(2007,4,9)),--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))))


--


Regards,


Peo Sjoblom




"sweens319" wrote in message
...
I'm not really sure how it's stored. The cells are formatted as "Custom"

m/d/yyy h:mm

Perhaps I am just dumb, but I can't seem to figure out how to incorporate
your suggestions (DATEVALUE, DATE, INT, TEXT, etc) into my formula.

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))

or

=COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*"))


"David Biddulph" wrote:

Assuming that your cells are not storing the dates and times as text
strings, but as Excel date & time values, then you'll need to convert and
process the data.

One option for your condition might be along the lines of either
INT(cell_ref)=DATEVALUE("9/4/2007") or better (to avoid ambiguities of
date
representation) INT(cell_ref)=DATE(2007,4,9)
Another option might be something like
TEXT(cell_ref,"d/m/yyyy")="9/4/2007"
--
David Biddulph

"sweens319" wrote in message
...
I got a little closer using COUNT:

=COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*"))

But that yielded a false answer as well. I got 2 when hitting <ENTER
and
1998 if I did the CSE (ctrl / shift / enter)

"sweens319" wrote:

I have a column that is formatted with a date and timestamp
10/4/2007 14:21

I want to find all the files that were completed on a specific date at
varying times.
I searched the help and found a little on criteria using wildcards,
but I
can't seem to figure out the correct way to write my formula.

I tried the following and keep getting a result of 0 (manual
calculation
proves the correct answer is 0):

$H2:H1000="9/2/2007*" - 0
$H2:H1000="9/2/2007"* - error
$H2:H1000=9/2/2007* - error

Perhaps my whole formula is incorrect somehow. I've got multiple
criteria,
one being matching a separate columns value to an array (thank you to
the
answer-people on my previous posts for making this possible for me).

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))

Compare/Find Exact Date match with varying time
 

Thank you all so much for all your help.

This worked perfectly except I reformatted my column to mm/dd/yyyy hh:mm and
then changed

DATE(2007,4,9) -- below
to
DATEVALUE("09/07/2007")

Before reformatting and changing this one little piece, it still resulted in
0 for some reason. Perhaps the format was just odd.

Thanks Again...


"Peo Sjoblom" wrote:

To get all values with the same date use something like

=SUMPRODUCT(--(INT(CSBD!H2:H1000)=DATE(2007,4,9)),--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))))


--


Regards,


Peo Sjoblom




"sweens319" wrote in message
...
I'm not really sure how it's stored. The cells are formatted as "Custom"

m/d/yyy h:mm

Perhaps I am just dumb, but I can't seem to figure out how to incorporate
your suggestions (DATEVALUE, DATE, INT, TEXT, etc) into my formula.

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))

or

=COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*"))


"David Biddulph" wrote:

Assuming that your cells are not storing the dates and times as text
strings, but as Excel date & time values, then you'll need to convert and
process the data.

One option for your condition might be along the lines of either
INT(cell_ref)=DATEVALUE("9/4/2007") or better (to avoid ambiguities of
date
representation) INT(cell_ref)=DATE(2007,4,9)
Another option might be something like
TEXT(cell_ref,"d/m/yyyy")="9/4/2007"
--
David Biddulph

"sweens319" wrote in message
...
I got a little closer using COUNT:

=COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*"))

But that yielded a false answer as well. I got 2 when hitting <ENTER
and
1998 if I did the CSE (ctrl / shift / enter)

"sweens319" wrote:

I have a column that is formatted with a date and timestamp
10/4/2007 14:21

I want to find all the files that were completed on a specific date at
varying times.
I searched the help and found a little on criteria using wildcards,
but I
can't seem to figure out the correct way to write my formula.

I tried the following and keep getting a result of 0 (manual
calculation
proves the correct answer is 0):

$H2:H1000="9/2/2007*" - 0
$H2:H1000="9/2/2007"* - error
$H2:H1000=9/2/2007* - error

Perhaps my whole formula is incorrect somehow. I've got multiple
criteria,
one being matching a separate columns value to an array (thank you to
the
answer-people on my previous posts for making this possible for me).

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))

Compare/Find Exact Date match with varying time
 

Well, of course, the two dates are different. The first is 9th April 2007,
whereas the second is either 9th July 2007 or 7th September 2007, depending
on your Windows Regional Settings. It is this latter ambiguity that leads
to the recommendation to use the DATE function, rather than DATEVALUE.
--
David Biddulph

"sweens319" wrote in message
...
Thank you all so much for all your help.

This worked perfectly except I reformatted my column to mm/dd/yyyy hh:mm
and
then changed

DATE(2007,4,9) -- below
to
DATEVALUE("09/07/2007")

Before reformatting and changing this one little piece, it still resulted
in
0 for some reason. Perhaps the format was just odd.

Thanks Again...

"Peo Sjoblom" wrote:

To get all values with the same date use something like

=SUMPRODUCT(--(INT(CSBD!H2:H1000)=DATE(2007,4,9)),--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))))

"sweens319" wrote in message
...
I'm not really sure how it's stored. The cells are formatted as
"Custom"

m/d/yyy h:mm

Perhaps I am just dumb, but I can't seem to figure out how to
incorporate
your suggestions (DATEVALUE, DATE, INT, TEXT, etc) into my formula.

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))

or

=COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*"))


"David Biddulph" wrote:

Assuming that your cells are not storing the dates and times as text
strings, but as Excel date & time values, then you'll need to convert
and
process the data.

One option for your condition might be along the lines of either
INT(cell_ref)=DATEVALUE("9/4/2007") or better (to avoid ambiguities of
date
representation) INT(cell_ref)=DATE(2007,4,9)
Another option might be something like
TEXT(cell_ref,"d/m/yyyy")="9/4/2007"
--
David Biddulph

"sweens319" wrote in message
...
I got a little closer using COUNT:

=COUNT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/4/2007*"))

But that yielded a false answer as well. I got 2 when hitting
<ENTER
and
1998 if I did the CSE (ctrl / shift / enter)

"sweens319" wrote:

I have a column that is formatted with a date and timestamp
10/4/2007 14:21

I want to find all the files that were completed on a specific date
at
varying times.
I searched the help and found a little on criteria using wildcards,
but I
can't seem to figure out the correct way to write my formula.

I tried the following and keep getting a result of 0 (manual
calculation
proves the correct answer is 0):

$H2:H1000="9/2/2007*" - 0
$H2:H1000="9/2/2007"* - error
$H2:H1000=9/2/2007* - error

Perhaps my whole formula is incorrect somehow. I've got multiple
criteria,
one being matching a separate columns value to an array (thank you
to
the
answer-people on my previous posts for making this possible for
me).

=SUMPRODUCT(--(ISNUMBER(MATCH(CSBD!K2:K1000,IDX,0))),--(CSBD!$H2:H1000="9/2/2007*"))