counting searched cells with date and time

  • Thread starter Thread starter mon0307
  • Start date Start date
M

mon0307

Hi. Could someone help me on this...
Here's my Sample:

A B

1 9/26/03 10:01 2
2 9/26/03 10:55
3 9/26/03 11:30

ok guys... Let's say I want to search and count only those that has
data entries of between 10-11am, meaning, I only need those with time
stamp of "10:", and I want to put the counted number in cell B1, where
there's suppose to be "2" because there's only 2 cells that has
"10:"... I don't know what function to use. I hope someone understand
my problem. Thanks and hoping for your immediate reply.
 
=COUNTIF(B1:B3,">="&E1)-COUNTIF(B1:B3,">"&F1)

where E1 houses the time value of 10:00 and F1 11:00.

Caveats. The formula executes an inclusive between and assumes that there is
always a corresponding date for each time value in B.
 
I can't see the picture you're referring too. Why don't you just state what
is wrong with the formula I suggested instead of creating new threads on the
same problem?
 
sorry about that. I've attached the picture (excel.jpg). I can see the
attachment how come you can't?
 
my problem is this:

CELL A
1 9/26/03 10:01
2 9/26/03 10:55
3 9/26/03 11:30

I need to count only the entries with the time between 10 and 11 am. I
can't separate the date from the time because it is entered "AS IS".
 
=SUMPRODUCT(((A1:A3-INT(A1:A3))>=C1)*((A1:A3-INT(A1:A3))<=D1))

where C1 houses the time value 10:00 and D1 11:00.
 
Back
Top