Looking for help...

I manage a closed ticket report and I need to find a formula that will find
all tickets in a certain time range.

For instance, in the list below, how would I write a formula to count all
tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not
important)?

H

TIME_ACTION_TAKEN
02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM
02/04/2009 8:46:08 AM
02/02/2009 4:29:31 PM
02/02/2009 6:59:39 PM
02/02/2009 6:24:48 PM
02/03/2009 1:46:16 AM
02/06/2009 3:49:10 PM
02/04/2009 10:40:35 AM
02/05/2009 2:04:34 PM
02/05/2009 2:11:17 PM
02/10/2009 6:18:17 PM
02/04/2009 7:56:45 PM
02/04/2009 8:27:22 PM
02/05/2009 7:03:33 PM
02/13/2009 9:51:25 AM

Appreciate the help!

Regards,

Ken

Not sure if you date is in column A and time in Column B or both the time and
date are in the same column


Column B contain just time
=SUMPRODUCT(--(B2:B17=TIME(15,0,0)),--(B2:B17<=TIME(18,0,0)))

If the time and date are both in column A there is two spaces between the
time and date which is causing a problem. I had to use the MID function to
get past the extra space. If you could eliminate the extra space it would
make the formula simplier.

=SUMPRODUCT(--(TIMEVALUE(MID(A2:A17,FIND("
",A2:A17)+1,LEN(A2:A17)))=TIME(15,0,0)),--(TIMEVALUE(MID(A2:A17,FIND("
",A2:A17)+1,LEN(A2:A17)))<=TIME(18,0,0)))



"Ken" wrote:

Looking for help...

I manage a closed ticket report and I need to find a formula that will find
all tickets in a certain time range.

For instance, in the list below, how would I write a formula to count all
tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not
important)?

H

TIME_ACTION_TAKEN
02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM
02/04/2009 8:46:08 AM
02/02/2009 4:29:31 PM
02/02/2009 6:59:39 PM
02/02/2009 6:24:48 PM
02/03/2009 1:46:16 AM
02/06/2009 3:49:10 PM
02/04/2009 10:40:35 AM
02/05/2009 2:04:34 PM
02/05/2009 2:11:17 PM
02/10/2009 6:18:17 PM
02/04/2009 7:56:45 PM
02/04/2009 8:27:22 PM
02/05/2009 7:03:33 PM
02/13/2009 9:51:25 AM

Appreciate the help!

Regards,

Ken

Hi Joel,

When I try the following it gives me a value error
"A value used in the formula is of the wrong data type"

=SUMPRODUCT(--(TIMEVALUE(MID(H2:H17,FIND("
",H2:H17)+1,LEN(H2:H17)))=TIME(15,0,0)),--(TIMEVALUE(MID(H2:H17,FIND("
",H2:H17)+1,LEN(H2:H17)))<=TIME(18,0,0)))

Unfortunately, I cannot remove the spaces to make the formula simpler. What
does the +1,LEN(H2:H17) portion do in this formula?

Best Regards,

"joel" wrote:

Not sure if you date is in column A and time in Column B or both the time and
date are in the same column


Column B contain just time
=SUMPRODUCT(--(B2:B17=TIME(15,0,0)),--(B2:B17<=TIME(18,0,0)))

If the time and date are both in column A there is two spaces between the
time and date which is causing a problem. I had to use the MID function to
get past the extra space. If you could eliminate the extra space it would
make the formula simplier.

=SUMPRODUCT(--(TIMEVALUE(MID(A2:A17,FIND("
",A2:A17)+1,LEN(A2:A17)))=TIME(15,0,0)),--(TIMEVALUE(MID(A2:A17,FIND("
",A2:A17)+1,LEN(A2:A17)))<=TIME(18,0,0)))



"Ken" wrote:

Looking for help...

I manage a closed ticket report and I need to find a formula that will find
all tickets in a certain time range.

For instance, in the list below, how would I write a formula to count all
tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not
important)?

H

TIME_ACTION_TAKEN
02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM
02/04/2009 8:46:08 AM
02/02/2009 4:29:31 PM
02/02/2009 6:59:39 PM
02/02/2009 6:24:48 PM
02/03/2009 1:46:16 AM
02/06/2009 3:49:10 PM
02/04/2009 10:40:35 AM
02/05/2009 2:04:34 PM
02/05/2009 2:11:17 PM
02/10/2009 6:18:17 PM
02/04/2009 7:56:45 PM
02/04/2009 8:27:22 PM
02/05/2009 7:03:33 PM
02/13/2009 9:51:25 AM

Appreciate the help!

Regards,

Ken

The MID function requires 3 parameters the last being the length. MID will
return either the number of character to the end of the string, or the number
of characters specified by the 3rd parameter (len in this case). I want to
reutrn all the character to the end of the string so I could of specified 256
characters to make the code simplier. I choose the length to use the length
of the string which will always be greater than the number of characters I
really need.

What the formula does is searches for the first space in the string and
returns all the character after the space. the FIND() part of the function
returns the character position of the space. I don't want the space in the
final results so I add 1 (+1) so I ignore the space.

I think the error is because of the 2 spaces. The TIMEVALUE isn't
recognizing the the time with the extra space. Try this change. I assume
your times are in cell H2 to H17. I tested both my old formula and new
formula with your posted data and didn't get an error. It is possible there
is some white characters (looks like spaces but aren't) in you string (like
tabs). I checked and my suspicion were correct. the two characters between
the date and time are 32 and 160 (32 + 128). Both are spaces. I have no
idea how you got a charcter 160! This formula will work in either case.

=SUMPRODUCT(--(TIMEVALUE(Trim(MID(H2:H17,FIND("
",H2:H17),LEN(H2:H17))))=TIME(15,0,0)),--(TIMEVALUE(trim(MID(H2:H17,FIND("
",H2:H17),LEN(H2:H17))))<=TIME(18,0,0)))


This is what I used to find the character 160
=CODE(MID($H$2,11,1)) - return 160
=CODE(MID($H$2,12,1)) - return 32
=CODE(MID($H$2,13,1)) - return 53 the character 5


"Ken" wrote:

Hi Joel,

When I try the following it gives me a value error
"A value used in the formula is of the wrong data type"

=SUMPRODUCT(--(TIMEVALUE(MID(H2:H17,FIND("
",H2:H17)+1,LEN(H2:H17)))=TIME(15,0,0)),--(TIMEVALUE(MID(H2:H17,FIND("
",H2:H17)+1,LEN(H2:H17)))<=TIME(18,0,0)))

Unfortunately, I cannot remove the spaces to make the formula simpler. What
does the +1,LEN(H2:H17) portion do in this formula?

Best Regards,

"joel" wrote:

Not sure if you date is in column A and time in Column B or both the time and
date are in the same column


Column B contain just time
=SUMPRODUCT(--(B2:B17=TIME(15,0,0)),--(B2:B17<=TIME(18,0,0)))

If the time and date are both in column A there is two spaces between the
time and date which is causing a problem. I had to use the MID function to
get past the extra space. If you could eliminate the extra space it would
make the formula simplier.

=SUMPRODUCT(--(TIMEVALUE(MID(A2:A17,FIND("
",A2:A17)+1,LEN(A2:A17)))=TIME(15,0,0)),--(TIMEVALUE(MID(A2:A17,FIND("
",A2:A17)+1,LEN(A2:A17)))<=TIME(18,0,0)))



"Ken" wrote:

Looking for help...

I manage a closed ticket report and I need to find a formula that will find
all tickets in a certain time range.

For instance, in the list below, how would I write a formula to count all
tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not
important)?

H

TIME_ACTION_TAKEN
02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM
02/04/2009 8:46:08 AM
02/02/2009 4:29:31 PM
02/02/2009 6:59:39 PM
02/02/2009 6:24:48 PM
02/03/2009 1:46:16 AM
02/06/2009 3:49:10 PM
02/04/2009 10:40:35 AM
02/05/2009 2:04:34 PM
02/05/2009 2:11:17 PM
02/10/2009 6:18:17 PM
02/04/2009 7:56:45 PM
02/04/2009 8:27:22 PM
02/05/2009 7:03:33 PM
02/13/2009 9:51:25 AM

Appreciate the help!

Regards,

Ken

Hi Joel,

I tried your suggested formula in the actual spreadsheet and in a blank
spreadsheet with the data below with no success. I'm still getting the same
error message even in the new spreadsheet using column A2-A17.

Your thoughts?

Best Regards,

"joel" wrote:

The MID function requires 3 parameters the last being the length. MID will
return either the number of character to the end of the string, or the number
of characters specified by the 3rd parameter (len in this case). I want to
reutrn all the character to the end of the string so I could of specified 256
characters to make the code simplier. I choose the length to use the length
of the string which will always be greater than the number of characters I
really need.

What the formula does is searches for the first space in the string and
returns all the character after the space. the FIND() part of the function
returns the character position of the space. I don't want the space in the
final results so I add 1 (+1) so I ignore the space.

I think the error is because of the 2 spaces. The TIMEVALUE isn't
recognizing the the time with the extra space. Try this change. I assume
your times are in cell H2 to H17. I tested both my old formula and new
formula with your posted data and didn't get an error. It is possible there
is some white characters (looks like spaces but aren't) in you string (like
tabs). I checked and my suspicion were correct. the two characters between
the date and time are 32 and 160 (32 + 128). Both are spaces. I have no
idea how you got a charcter 160! This formula will work in either case.

=SUMPRODUCT(--(TIMEVALUE(Trim(MID(H2:H17,FIND("
",H2:H17),LEN(H2:H17))))=TIME(15,0,0)),--(TIMEVALUE(trim(MID(H2:H17,FIND("
",H2:H17),LEN(H2:H17))))<=TIME(18,0,0)))


This is what I used to find the character 160
=CODE(MID($H$2,11,1)) - return 160
=CODE(MID($H$2,12,1)) - return 32
=CODE(MID($H$2,13,1)) - return 53 the character 5


"Ken" wrote:

Hi Joel,

When I try the following it gives me a value error
"A value used in the formula is of the wrong data type"

=SUMPRODUCT(--(TIMEVALUE(MID(H2:H17,FIND("
",H2:H17)+1,LEN(H2:H17)))=TIME(15,0,0)),--(TIMEVALUE(MID(H2:H17,FIND("
",H2:H17)+1,LEN(H2:H17)))<=TIME(18,0,0)))

Unfortunately, I cannot remove the spaces to make the formula simpler. What
does the +1,LEN(H2:H17) portion do in this formula?

Best Regards,

"joel" wrote:

Not sure if you date is in column A and time in Column B or both the time and
date are in the same column


Column B contain just time
=SUMPRODUCT(--(B2:B17=TIME(15,0,0)),--(B2:B17<=TIME(18,0,0)))

If the time and date are both in column A there is two spaces between the
time and date which is causing a problem. I had to use the MID function to
get past the extra space. If you could eliminate the extra space it would
make the formula simplier.

=SUMPRODUCT(--(TIMEVALUE(MID(A2:A17,FIND("
",A2:A17)+1,LEN(A2:A17)))=TIME(15,0,0)),--(TIMEVALUE(MID(A2:A17,FIND("
",A2:A17)+1,LEN(A2:A17)))<=TIME(18,0,0)))



"Ken" wrote:

Looking for help...

I manage a closed ticket report and I need to find a formula that will find
all tickets in a certain time range.

For instance, in the list below, how would I write a formula to count all
tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not
important)?

H

TIME_ACTION_TAKEN
02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM
02/04/2009 8:46:08 AM
02/02/2009 4:29:31 PM
02/02/2009 6:59:39 PM
02/02/2009 6:24:48 PM
02/03/2009 1:46:16 AM
02/06/2009 3:49:10 PM
02/04/2009 10:40:35 AM
02/05/2009 2:04:34 PM
02/05/2009 2:11:17 PM
02/10/2009 6:18:17 PM
02/04/2009 7:56:45 PM
02/04/2009 8:27:22 PM
02/05/2009 7:03:33 PM
02/13/2009 9:51:25 AM

Appreciate the help!

Regards,

Ken

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


"Ken" wrote in message
...
Looking for help...

I manage a closed ticket report and I need to find a formula that will
find
all tickets in a certain time range.

For instance, in the list below, how would I write a formula to count all
tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not
important)?

H

TIME_ACTION_TAKEN
02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM
02/04/2009 8:46:08 AM
02/02/2009 4:29:31 PM
02/02/2009 6:59:39 PM
02/02/2009 6:24:48 PM
02/03/2009 1:46:16 AM
02/06/2009 3:49:10 PM
02/04/2009 10:40:35 AM
02/05/2009 2:04:34 PM
02/05/2009 2:11:17 PM
02/10/2009 6:18:17 PM
02/04/2009 7:56:45 PM
02/04/2009 8:27:22 PM
02/05/2009 7:03:33 PM
02/13/2009 9:51:25 AM

Appreciate the help!

Regards,

Ken

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


"Ken" wrote in message
...
Looking for help...

I manage a closed ticket report and I need to find a formula that will
find
all tickets in a certain time range.

For instance, in the list below, how would I write a formula to count all
tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not
important)?

H

TIME_ACTION_TAKEN
02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM
02/04/2009 8:46:08 AM
02/02/2009 4:29:31 PM
02/02/2009 6:59:39 PM
02/02/2009 6:24:48 PM
02/03/2009 1:46:16 AM
02/06/2009 3:49:10 PM
02/04/2009 10:40:35 AM
02/05/2009 2:04:34 PM
02/05/2009 2:11:17 PM
02/10/2009 6:18:17 PM
02/04/2009 7:56:45 PM
02/04/2009 8:27:22 PM
02/05/2009 7:03:33 PM
02/13/2009 9:51:25 AM

Appreciate the help!

Regards,

Ken

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


"Ken" wrote in message
...
Looking for help...

I manage a closed ticket report and I need to find a formula that will
find
all tickets in a certain time range.

For instance, in the list below, how would I write a formula to count
all
tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not
important)?

H

TIME_ACTION_TAKEN
02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM
02/04/2009 8:46:08 AM
02/02/2009 4:29:31 PM
02/02/2009 6:59:39 PM
02/02/2009 6:24:48 PM
02/03/2009 1:46:16 AM
02/06/2009 3:49:10 PM
02/04/2009 10:40:35 AM
02/05/2009 2:04:34 PM
02/05/2009 2:11:17 PM
02/10/2009 6:18:17 PM
02/04/2009 7:56:45 PM
02/04/2009 8:27:22 PM
02/05/2009 7:03:33 PM
02/13/2009 9:51:25 AM

Appreciate the help!

Regards,

Ken

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


"Ken" wrote in message
...
Looking for help...

I manage a closed ticket report and I need to find a formula that will
find
all tickets in a certain time range.

For instance, in the list below, how would I write a formula to count
all
tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not
important)?

H

TIME_ACTION_TAKEN
02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM
02/04/2009 8:46:08 AM
02/02/2009 4:29:31 PM
02/02/2009 6:59:39 PM
02/02/2009 6:24:48 PM
02/03/2009 1:46:16 AM
02/06/2009 3:49:10 PM
02/04/2009 10:40:35 AM
02/05/2009 2:04:34 PM
02/05/2009 2:11:17 PM
02/10/2009 6:18:17 PM
02/04/2009 7:56:45 PM
02/04/2009 8:27:22 PM
02/05/2009 7:03:33 PM
02/13/2009 9:51:25 AM

Appreciate the help!

Regards,

Ken

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


"Ken" wrote in message
...
Looking for help...

I manage a closed ticket report and I need to find a formula that
will
find
all tickets in a certain time range.

For instance, in the list below, how would I write a formula to
count
all
tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is
not
important)?

H

TIME_ACTION_TAKEN
02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM
02/04/2009 8:46:08 AM
02/02/2009 4:29:31 PM
02/02/2009 6:59:39 PM
02/02/2009 6:24:48 PM
02/03/2009 1:46:16 AM
02/06/2009 3:49:10 PM
02/04/2009 10:40:35 AM
02/05/2009 2:04:34 PM
02/05/2009 2:11:17 PM
02/10/2009 6:18:17 PM
02/04/2009 7:56:45 PM
02/04/2009 8:27:22 PM
02/05/2009 7:03:33 PM
02/13/2009 9:51:25 AM

Appreciate the help!

Regards,

Ken

=SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2))
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."

Just ignore that. It's not an error.

Can you help me understand what this
portion of the formula is doing ",13,12"?

Let's look at your data:

02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM

Your data is TEXT. It is not true Excel date/time values (which are really
just numbers formatted to look like dates/times).

Assuming the date *always* follows this format (which is how your sample
data is) dd/mm/yyyy. So the date portion of the string is *always* 10
characters long. Then the date is followed by 2 whitespace characters. This
means that the time portion of the string starts at character number 13. So
we tell the MID function to start at character number 13 and extract the
next 12 characters.

The time portion of the string can be either 10 or 11 characters long.
Extracting 12 characters ensures that we get the whole time portion
extracted.

So, this is what the MID function looks like when it extracts the time
portion:

MID
(
"5:09:32 PM"
"10:48:03 AM"
)

The MID function returns a TEXT string even if it's a number so we have to
convert that TEXT number into a numeric number. The double unary "--"
coerces these TEXT numbers into the numeric values that represent the times:

--"5:09:32 PM" = 0.714953703703704
--"10:48:03 AM" = 0.450034722222222

So, with D2 and E2 holding the time boundaries:

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

The formula is comparing:

0.714953703703704; 0.450034722222222 = 0.625
0.714953703703704; 0.450034722222222 <= 0.75

Based on this limited sample the result is 1.

0.714953703703704 ("5:09:32 PM") is = 0.625 (3:00 PM) and <= 0.75 (6:00 PM)


--
Biff
Microsoft Excel MVP


"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


"Ken" wrote in message
...
Looking for help...

I manage a closed ticket report and I need to find a formula that will
find
all tickets in a certain time range.

For instance, in the list below, how would I write a formula to count
all
tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not
important)?

H

TIME_ACTION_TAKEN
02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM
02/04/2009 8:46:08 AM
02/02/2009 4:29:31 PM
02/02/2009 6:59:39 PM
02/02/2009 6:24:48 PM
02/03/2009 1:46:16 AM
02/06/2009 3:49:10 PM
02/04/2009 10:40:35 AM
02/05/2009 2:04:34 PM
02/05/2009 2:11:17 PM
02/10/2009 6:18:17 PM
02/04/2009 7:56:45 PM
02/04/2009 8:27:22 PM
02/05/2009 7:03:33 PM
02/13/2009 9:51:25 AM

Appreciate the help!

Regards,

Ken

Thanks for taking the time to explain everything.... it makes sense now!

I'm going to try the augmented formula now.

Best Regards,

"T. Valko" wrote:

=SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2))
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."

Just ignore that. It's not an error.

Can you help me understand what this
portion of the formula is doing ",13,12"?

Let's look at your data:

02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM

Your data is TEXT. It is not true Excel date/time values (which are really
just numbers formatted to look like dates/times).

Assuming the date *always* follows this format (which is how your sample
data is) dd/mm/yyyy. So the date portion of the string is *always* 10
characters long. Then the date is followed by 2 whitespace characters. This
means that the time portion of the string starts at character number 13. So
we tell the MID function to start at character number 13 and extract the
next 12 characters.

The time portion of the string can be either 10 or 11 characters long.
Extracting 12 characters ensures that we get the whole time portion
extracted.

So, this is what the MID function looks like when it extracts the time
portion:

MID
(
"5:09:32 PM"
"10:48:03 AM"
)

The MID function returns a TEXT string even if it's a number so we have to
convert that TEXT number into a numeric number. The double unary "--"
coerces these TEXT numbers into the numeric values that represent the times:

--"5:09:32 PM" = 0.714953703703704
--"10:48:03 AM" = 0.450034722222222

So, with D2 and E2 holding the time boundaries:

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

The formula is comparing:

0.714953703703704; 0.450034722222222 = 0.625
0.714953703703704; 0.450034722222222 <= 0.75

Based on this limited sample the result is 1.

0.714953703703704 ("5:09:32 PM") is = 0.625 (3:00 PM) and <= 0.75 (6:00 PM)


--
Biff
Microsoft Excel MVP


"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


"Ken" wrote in message
...
Looking for help...

I manage a closed ticket report and I need to find a formula that will
find
all tickets in a certain time range.

For instance, in the list below, how would I write a formula to count
all
tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not
important)?

H

TIME_ACTION_TAKEN
02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM
02/04/2009 8:46:08 AM
02/02/2009 4:29:31 PM
02/02/2009 6:59:39 PM
02/02/2009 6:24:48 PM
02/03/2009 1:46:16 AM
02/06/2009 3:49:10 PM
02/04/2009 10:40:35 AM
02/05/2009 2:04:34 PM
02/05/2009 2:11:17 PM
02/10/2009 6:18:17 PM
02/04/2009 7:56:45 PM
02/04/2009 8:27:22 PM
02/05/2009 7:03:33 PM
02/13/2009 9:51:25 AM

Appreciate the help!

Regards,

Ken

Hi T.Valko,

I was able to get your formula to work in a basic spreadsheet with just the
data below. Thanks for your help. Now I need to find out why it won't work in
my active spreadsheet. I'll keep you posted.

Regards,

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


"Ken" wrote in message
...
Looking for help...

I manage a closed ticket report and I need to find a formula that will
find
all tickets in a certain time range.

For instance, in the list below, how would I write a formula to count all
tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not
important)?

H

TIME_ACTION_TAKEN
02/10/2009 5:09:32 PM
02/02/2009 10:48:03 AM
02/04/2009 8:46:08 AM
02/02/2009 4:29:31 PM
02/02/2009 6:59:39 PM
02/02/2009 6:24:48 PM
02/03/2009 1:46:16 AM
02/06/2009 3:49:10 PM
02/04/2009 10:40:35 AM
02/05/2009 2:04:34 PM
02/05/2009 2:11:17 PM
02/10/2009 6:18:17 PM
02/04/2009 7:56:45 PM
02/04/2009 8:27:22 PM
02/05/2009 7:03:33 PM
02/13/2009 9:51:25 AM

Appreciate the help!

Regards,

Ken