patients per hour

[dr350x]

hello,
newer to excel 03, i have data from my ER, i cant figure how to pull out the
number of patients per hour. i can do it manualy but this is tedious. would
like to automat process, pivot or formula?
eg: pts presents at
07:00
07:10 3 patients from 07:00 to 07:59
07:12
08:15
08:17 2 patients from 08:00 to 08:59

this goes on over 24 hours. military time.

i have tried different formulas, IF,AND,OR functions, i tried conditional
formating. i failed miserably at these. i m to new at excel to know the
nuances. i have done many searches on this forum and found and solved 95% of
the issues in needed to solve. any help would be greatly appreciated. thank
you in advance! dr350x

 

[Gary''s Student]

From your posted data it appears that time is in column A and the number of
patients for each hour is in column B:

1. make sure that the sheet represents only a single 24 hour period. (use a
new sheet for each day)

2. in an un-used cell, enter:

=SUM(B:B)/24

 

[Dom_Ciccone]

Assuming that your range of data starts at A1, the formula you need to count
those between 7am and 8am is:

=COUNTIF($A$1:$A$5,">="&TIME(7,0,0))-COUNTIF($A$1:$A$5,">="&TIME(8,0,0))

To find those between 8am and 9am, simply change the formula to read:

=COUNTIF($A$1:$A$5,">="&TIME(8,0,0))-COUNTIF($A$1:$A$5,">="&TIME(9,0,0))

Apply the equivalent formulae to the other 22 cells so that you have one
formula for each of the 24 hours in the period. Remember to use 13,14,15 etc
for the 24-hour clock.

This should be what you need.

 

[Dom_Ciccone]

Meant to point out that you will need to adjust the range for your list of
times. It might be easier to use a named range in the formula and then just
use Insert--Name--Define to adjust the area that the name refers to.

 

[dr350x]

gary, thanks'
ive done that, let me clarify further, what i am trying to do is pull out
the number of patients the are registered per hour. i am trying to automate
this process.

i think i need arguments like , IF pt registred between 0700 and 0759,
place total number of patients for that hour in comlum b.

i need to do this over a 24 hour time frame.
does that make sence? i dont quite know how to do that...thanks for helping
me.

 

[dr350x]

thanks,dom
i put the formula in, it seems to be counting only on cell at a time, i have
mult patients per hour, how do i get it count all between the range of say
0800 to 0900. rows would be, say from 15 to 22 ( 7 pts in that hour).
i think in understand whats happening with the formula.
-- thank you
dr350x

 

[Dom_Ciccone]

Okay. I'm assuming you have a list of times that patients attended your ER
and that this list appears in column A, such as:

A
1 07:20
2 07:24
3 08:13
4 08:43

Highlight the entire list of data and name the range "PTTimes"
(Insert--Name--Define).

In cell B1, type this formula:

=COUNTIF(PTTimes,">="&TIME(7,0,0))-COUNTIF(PTTimes,">="&TIME(8,0,0))

This should return the correct count of patients attending between 7am and
8am.

Is it not doing that?

 

[Teethless mama]

Assuming your data in column A (A2:A100)
create a helper column in (Column B)
B2: holds 7:00
B3: holds 8:00
B4: holds 9:00
and so on...to 24:00

In C2: =SUMPRODUCT(--($A$2:$A$100>=$B2),--($A$2:$A$100<$B3))

Drag the Fill Handle in C2 to copy as far as needed

 

[dr350x]

dom, thank you,
your asumption on colum layout is correct.
i inputed the formula, i did it for one 24hr period. i did it for on hour
1100 to 1200. i had 7 patients that hour, the formula returned 148. B cell
formated to gen number. i think i understand what it trying to do, but i cant
figure why it came up with that total. any thoughts?

 

[dr350x]

teethless,
thanks i 'll try it this week....