Counting patient check in

[Meebers]

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

 

[Luke M]

A few assumptions:
You have a table somewhere with at least one columns which contains time
stamps (properly inputted as time, not text).

you then have a report table setup somewhere with 1st column(say, column A
on sheet1) containing hours, such as the following, let say starting in cell
a2:
6:00 am
7:00 am
.....
6:00 pm

Note that I'm assuming there are no time stamps AFTER or on 6 pm. If you
want inclusive, change time to 6:01 pm.

in B2, your formula is then:
=COUNTIF('Sheet1'!A:A,">="&A2)-COUNTIF('Sheet1'!A:A,">="&A3)

You can then copy this down, and it will give you counts correlating to
signing between the two boundaries.

 

[T. Valko]

Times in column A...

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

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

 

[Meebers]

Luke..here are my results. your assumptions are correct. I copied the time
column and pasted it starting in A2, pasted your formula in B2 the results
are like this:
6:45 1
6:49 0
6:49 2

Expected answer 6=3, even though I can add 1+0+2 to get 3, it is just as
easy as counting the interval by eye. Unless I missed something?? Thanks
for your comment...

 

[Meebers]

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]

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))

 

[Meebers]

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?

 

[Meebers]

ok...figured it out, all answers should be 4's within the 6 am test
sheet....Tx for help, got a working solution now.

 

[T. Valko]

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.

 

[Shane Devenshire]

Please don't post questions without questions, Biff may be a mind reader but
the rest of us are mere mortals.

 

[Meebers]

Biff...I used your first formula "=SUMPRODUCT(--(HOUR(A1:A100)=6))" and
when I drug it down in the test sheet the formula became A2:A101and so on
giving the decrementing count. I then "fixed it" by adding the $. I am
now using the last formula you sent and everything is working as expected.
I created a sheet and left the A column blank, cut and paste from my data
sheet to the A column, and then copy/paste special/value the results and
move onto the next data sheet. Easy to get the totals for 5 am etc. Had to
change the formula to $A2:$A250. Tx for hanging in there with me.

 

[T. Valko]

Ok, glad you got it straightened out. Thanks for the feedback!

 

[Jacob Skaria]

"Meebers"

1. Check whether the entries in A2:A30 are in time format.

2. Refer the cell in formula. Suppose from C2 to C13 enter 6,7,8,... and D2
enter the formula
=SUMPRODUCT(--(HOUR($A$2:$A$30)=C2)) and copy that down..

If this post helps click Yes

 

[Meebers]

Jacob, I used the same test data (nice spread from 6 am thru 6 pm) with
Biff's formula, the results were correct. When I used your method, same
data, I got the following answers

6 5 (correct)
7 10 (correct)
8 14 (correct)
9 0 and from here on, all counts were zero. (9 should have been 10)

Data was in time format 24 hr, formated as h:mm (this data worked in Biffs
formula), C2 thru C13 were 6,7,8...17

Thanks for your response.