Looking for formula
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
|