6:45 4
6:46 3
6:47 2
6:51 1
7:01 0

I'm not sure what you're doing to get those results.

Where do you want the results to appear? You said something about "dragging
horizontally" in your other reply so I'm assuming that means you want the
results across a row.

Let's assume these are your time entries:

A2 = 6:45
A3 = 6:46
A4 = 6:47
A5 = 6:51
A6 = 7:01

When you leave out the AM/PM portion of a time entry Excel defaults to AM.

Let's assume you you have some column headers that represent the hourly
intervals:

C1 = 6:00 AM
D1 = 7:00 AM
E1 = 8:00 AM

This formula entered in C2 and copied across to E2:

=SUMPRODUCT(--(HOUR($A2:$A6)=HOUR(C1)))

Returns: 4, 1, 0

Based on true Excel times.


--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
Biff...I will except that for some reason, it is not true Excel time. I
made a test sheet with data in A1 your original formula in B1 here are my
results

6:45 4
6:46 3
6:47 2
6:51 1
7:01 0

I can see that the first result B1 = 4 which is correct for the number of
6 am's, not sure how to read the other 3 or disgard?


"T. Valko" wrote in message
...
If you got a #VALUE! error then your times aren't true Excel times, or,
you may have other TEXT entries in the range.

dragging it horizontal and changing the last number to 7...8 etc??

Incrementing the hour number could be done easily:

=SUMPRODUCT(--(HOUR($A2:$A30)=COLUMNS($A1:F1))


--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
Biff, with times in A2:A30 I pasted in your formula and got the #VALUE.
IF this only counts 6-6:59 I would have to paste in 12 different
formulas?? by dragging it horizontal and changing the last number to
7...8 etc??


"T. Valko" wrote in message
...
Times in column A...

=SUMPRODUCT(--(HOUR(A1:A100)=6))

Will count times from 6:00:00 AM to 6:59:59 AM

--
Biff
Microsoft Excel MVP


"Meebers" wrote in message
...
We have a high number of patients that come in between 6 am and 6 pm
and we need to count the times that they sign in. i.e. Every patient
that signs in at 6 am thru 6:59am I want a total. 7am thru 7:59am I
want a total....etc. Data is generated daily on a seperate sheet that
I have to count so was looking for a "portable formula" that I can
paste and get the time breakouts. Expected results 6=23 7=34 8=61
etc. (meaning there were 23 patients between 6 am and 6:59) TIA