Counting Admission during a Time Period

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

Hi,
I have a monthly workbook with 2 wooksheet, 1 sheet with admission times of
patients ie. 08:02, 14:34 etc in column and the row with dates ie. 3rd
March, 4th March etc., the other sheet I would like excel to count the
number of admissions between a certain time scan ie. 08:00 to 12:00, 12:01
to 16:00 and all occurnaces on each day of the month. I am currently
entering the data manually using custom filter on Admission Time and Date,
this means I have to count the numnber of entries manually, is there a way
(formula) so the excel counts them for me.
Thanks for your help

Sandy
 
Hi Sandy try
(if col. A stores your time)
=SUMPRODUCT(--(A1:A100>=TIME(8,0,0)),--(A1:A100<TIME(12,0,0)))

for a specific date use (if col. B stores the date)
=SUMPRODUCT(--(A1:A100>=TIME(8,0,0)),--(A1:A100<TIME(12,0,0)),--(B1:B10
0=DATE(2004,4,19)))
 
Hi Frank, thanks for your help again. I can get the first formula to work
but am unable to get the second one to work.I have altered to suit by
worksheet but still it doesn't work, here is my formula
=SUMPRODUCT(--(K4:K59>=TIME(8,0,0)),--(B4:B59<TIME(12,0,0)),--(K4:K59=DATE(2
004,3,3))) I enter as an array formula. If it was working it would give me 2
occurances (09:09 and 09:24 on 3rd March) but it returns a zero value. Any
ideas? Thanks again you're a genius
sandy
 
Hi Sandy
- first: no need for an array entry
- second: I assume that column K contains a date/time value? and the
same is true for column B. If yes, try
=SUMPRODUCT(--(MOD(K4:K59,1)>=TIME(8,0,0)),--(MOD(B4:B59,1)<TIME(12,0,0
)),--(INT(K4:K59)=DATE(2004,3,3)))

Though I'm not sure about your use of column B?
 
Back
Top