If anyone is still following this thread...

Problem solved!

The OP's data *already* was true Excel dates/times.

When this data was pasted into the forum and then copy/pasted into a test
file it was pasted as TEXT which lead those of us that replied to craft
formulas based on the data being text (which it was in *our* test files).

The working formula:

=SUMPRODUCT(--(MOD(range,1)=x),--(MOD(range,1)<x))

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Hi Biff,

My findings/update was sent to you this morning. I've made some progress.

Regards,

Ken

"T. Valko" wrote:

OK, received the email and replied.

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Sure thing...

I sent you a test email, just respond back to it so I know I have the
right
addy.

Appreciate the help!

Regards,

Ken

"T. Valko" wrote:

Can you send me a copy of your file with the original data? If it
contains
sensitive data you can delete it. All I really need to see are these
mysterous dates/times.

If you want to do that my address is:

xl can help at comcast period net

Remove "can" and change the obvious.

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Ok my findings are as follows:

I tried your macro and was able to get it working when selecting the
data
A2:A17 within your spreadsheet but when I tried it within my
spreadsheet
on
the Data (Raw) it didn't do anything but blink.

My Troubleshooting Steps...

I copied the Data (Raw) tab from my spreadsheet to yours
"dAv7mcvljA_xKen.xls" and ran the "TrimAll" macro but got the same
result,
it
just blinked.

I was able to get the formula you provided and the macro to work
using
the
data when it was in a Text Format but when trying it on the client
data
it
doesn't see it.

I tried changing the cell format (right click Format Cells) to
General
but
it just changes the date/time to a number (i.e. 02/09/2009 6:53:36
PM
turns
into 39853.78722)

My guess is that the underlying data is in numerical format with no
spaces
which is why the macro doesn't do anything.

When I right click the client data and select Format Cells it shows
Custom
Format: mm/dd/yyyy" "h\:mm\:ss AM/PM".


Is there something that can be added to the formula to
detect/recognize
the
time portion of the data in numerical format?

Hopefully, what I wrote makes sense to you.

Ken


"T. Valko" wrote:

So far, the formulas are all correct. It's your data that's the
problem!
There may be additional unseen whitespace characters that we
haven't
been
able to pinpoint.

Instead of "farting around" and trying to get a convoluted formula
to
handle
the data why don't you do this...

There is a macro by MVP David McRitchie that will clean all these
whitespaces from your data and in the process will convert your
TEXT
date/times to true Excel numeric date/times.

I have put together a small sample file that demonstrates how this
works.

I copied/pasted your sample data into the sample file. I installed
the
macro
and entered the formula in a cell.

The formula currently returns an error because the data is TEXT.
Once
you
run the macro the formula will return the correct result.

For this to work...

Macros must be enabled. To view the macro code:

Hit ALT F11. This will open the VBE.
Hit CTRL R. This will open the Project Explorer pane on the left
side
of
the
window.
In the Project Explorer look for the file name VBAProject
(xKen.xls).
In the subdirectories of that file you'll see Module1
Double click Module1
The macro code will appear in the window on the right.
After you've examined the code return to Excel, hit ALT Q

To run the macro
Select the range of data you want to clean, A2:A17.
Goto the menu ToolsMacroMacros
Select the TrimALL macro
Click RUN

Watch what happens.

I do a lot of copy/pasting from the web and the data always
contains
these
unseen whitespace characters. So I use this macro all the time. I
use
it
so
often I put a button on one of my toolbars and attached this macro
to
the
button so it's easy to use. This macro is a real time (and
headache)
saver!

Here's the sample file:

xKen.xls 24kb

http://cjoint.com/?dAv7mcvljA

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
The data comes from a client's call database so I guess you could
say
it
comes from another application. The formula you provided should
work
...
it
all makes sense but I cannot get past the following error:

"A value used in the formula is of the wrong data type"

This is the formula that I'm using...

=SUMPRODUCT(--(--MID('DATA
(RAW)'!H2:H302,13,12)=D2),--(--MID('Data
(RAW)'!H2:H302,13,12)<E2))

Do you see anything that I may be doing wrong?

Regards,

Ken


"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(--MID('Data
(RAW)'!A2:A17,13,12)=D2),--(--MID('Data
(RAW)'!A2:A17,13,12)<=E2))

Does this data come from another application or the web? That
would
explain
why the data is TEXT and has extra whitspace characters. If this
is
the
case
it would be easier to "clean" the data. Let me know if you are
interested
in
this.


--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Hi David,

I need to point the formula to a separate "Data (RAW)" tab in
the
formula,
do you know how I would write it? If column A resides on a
different
tab
named Data (Raw) how would I add it to the formula below?

=SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2))


Appreciate the help!

Regards,

"David Biddulph" wrote:

The 13,12 are the second and third arguments of the MID
function.
MID
is
a
standard Excel function and its syntax is explained (with
examples)
in
Excel
help.

You probably don't need to worry about the warning "The
formula
in
this
cell
refers to a range that has additional numbers adjacent to
it.",
providing
that you are confident that you have included the correct
cells
in
your
formula.
--
David Biddulph

"Ken" wrote in message
...
Hi T. Valko,

Am I the only one having probems with this web forum? I'm
having
problems
expanding the discussion list and when I click on the names
it
shows
blank.
Anyway, I need to get back to the problem at hand.

Your suggestion is perfect ... if I could get it to work.
Can
you
help
me
understand what this portion of the formula is doing
",13,12"?

When I tried your formula it returned a value of 0 and gave
me
the
following
error:

"The formula in this cell refers to a range that has
additional
numbers
adjacent to it."

Appreciate the help!

Ken

"T. Valko" wrote:

Assuming you dates/times are all in the same column and
they're
really
TEXT
strings:

Use cells to hold your time boundaries:

D2 = 3:00 PM
E2 = 6:00 PM

=SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2))

--
Biff
Microsoft Excel MVP