Bed Occupancy rates

[joblej]

Any help gratefully received.
I am trying to calculate the bed occupancy rates on a hospital ward for each day.

I have the patient ID, admission date [dd/mm/yyyy] and discharge date.

For example, on the 02/01/1970, there was only one patient on the ward, on the 04/01/1970 there were 4 patients, 06/01/1970 2 patients. Is there some way of automatically calculating this figure?

Thanks in advance.

J

 

[Trouttrap2]

You will need to enter a date in a cell and use that as your criteria that will determine the count.
A B C D E
<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=320 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Admin</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Discharge</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Date</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Count</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="25569">1/1/1970</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="25689">5/1/1970</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="25659">4/1/1970</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="25628">3/1/1970</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="25781">8/1/1970</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="25628">3/1/1970</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="25873">11/1/1970</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="25628">3/1/1970</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="25689">5/1/1970</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

Using formula =SUMPRODUCT(--($D$3>=$A$3:$A$6),--($D$3<=$B$3:$B$6)) will count the occurances for 4/1/70. You will need to adjust your ranges accordingly.

 

[MickG]

Try this :- Results start "D1"

[COLOR=navy]Sub[/COLOR] MG21Mar34
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] St [COLOR=navy]As[/COLOR] Date, oEnd [COLOR=navy]As[/COLOR] Date, n [COLOR=navy]As[/COLOR] Date
[COLOR=navy]Dim[/COLOR] p [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
p = 1
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))
 St = Application.min(Rng.Offset(, 1).Resize(, 2))
    oEnd = Application.Max(Rng.Offset(, 1).Resize(, 2))
      ReDim Ray(1 To oEnd - St + 2, 1 To 2)
            Ray(1, 1) = "Date": Ray(1, 2) = "Beds in Use"
[COLOR=navy]For[/COLOR] n = St To oEnd
        p = p + 1
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]If[/COLOR] n >= Dn.Offset(, 1) And n <= Dn.Offset(, 2) [COLOR=navy]Then[/COLOR]
            c = c + 1
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Dn
        Ray(p, 1) = Format(n, "dd/mm/yyyy"): Ray(p, 2) = c
        c = 0
[COLOR=navy]Next[/COLOR] n
    Range("D1").Resize(p, 2) = Ray
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Data & Results.

[COLOR=royalblue][B]Row No [/B][/COLOR][COLOR=royalblue][B]Col(A) [/B][/COLOR][COLOR=royalblue][B]Col(B)     [/B][/COLOR][COLOR=royalblue][B]Col(C)     [/B][/COLOR][COLOR=royalblue][B]Col(D)     [/B][/COLOR][COLOR=royalblue][B]Col(E)      [/B][/COLOR]
1.      Name    Admitted   DisCharged  Date        Beds in Use 
2.      Nam1    01/01/1970  05/01/1970  01/01/1970  1           
3.      Nam2    03/01/1970  08/01/1970  02/01/1970  1           
4.      Nam3    03/01/1970  11/01/1970  03/01/1970  4           
5.      Nam4    03/01/1970  05/01/1970  04/01/1970  4           
6.                                      05/01/1970  4           
7.                                      06/01/1970  2           
8.                                      07/01/1970  2           
9.                                      08/01/1970  2           
10.                                     09/01/1970  1           
11.                                     10/01/1970  1           
12.                                     11/01/1970  1

Regards Mick

 

[joblej]

Thank you very much for your help.
That certainly does the trick!

 

[giles]

Hi I have been able to get the script, detailed on Jan 2008, to partially work however the results are not quite right.
I'm wondering if it is the different versions I'm using 2010 Excel
The dates are not calculating correctly, seems to be on US dates system even though the format seem ok (Ray(p, 1) = Format(n, "dd/mm/yyyy"): Ray(p, 2) = c)
I've only selected data for January 2012 and the script is producing results for August and other months; the data for some of January looks spot on - oh so close!!
Couls anyone assist?
Many thanks