Compare/Find Exact Date match with varying time

[Guest]

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*"))

 

[Guest]

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)

 

[Guest]

Try this:

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

 

[Guest]

Getting:

#N/A

using that one.

Try this:

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

 

[David Biddulph]

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"

 

[Guest]

Put quotes around the "IDX"

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

 

[Guest]

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*"))

 

[Guest]

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.

 

[Peo Sjoblom]

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

 

[Guest]

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

 

[David Biddulph]

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.