occupancy

MaxPower4

New Member
Joined
Sep 25, 2011
Messages
3
Hi guys i am trying to develop a report that will show the occupancy of an emergency department by hour for each day (ie how many patients in the department each hour).

I have admission number, admission date/time [dd/mm/yyyy hh:mm], discharge date/time [dd/mm/yyyy hh:mm].

I wanted to show the trends in the number of patients in the department, and doing this by the hour will show this.

Anyone help with this?? I have office 2003.

Thanks in advance!!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
A Histogram for certain time periods say every two hours of the day will allow you to simplify the raw data to a point (12 bars).

Additionally you could minus admission from discharge to get the time spent then average it for certain periods.

Or you could use a standard chart with secondary axis to have admissions per time period on one axis and the average time before discharge on the other axis for those admissions.

Trends usually have to have something in common.

cheers
 
Upvote 0
Thanks for the prompt response. The file looks like the type of thing i am trying to do. How do you get this to setup in a new workbook - is there some settings or add-ons that i need to get this to work??
 
Upvote 0
I think I've got this right(?):
Excel Workbook
ABCDEF
5no.admitteddischargedstart of houroccupancy
6123 Sep 08:3023 Sep 15:0023 Sep 08:002
7223 Sep 08:4123 Sep 14:1823 Sep 09:005
8323 Sep 09:0923 Sep 10:3123 Sep 10:009
9423 Sep 09:3223 Sep 10:4123 Sep 11:0011
10523 Sep 09:5223 Sep 11:2623 Sep 12:0013
11623 Sep 10:0223 Sep 10:3523 Sep 13:0015
12723 Sep 10:0823 Sep 13:3523 Sep 14:0017
13823 Sep 10:1923 Sep 16:5223 Sep 15:0019
14923 Sep 10:3923 Sep 15:1023 Sep 16:0019
151023 Sep 11:0223 Sep 12:4523 Sep 17:0017
161123 Sep 11:2623 Sep 18:3323 Sep 18:0018
171223 Sep 11:4023 Sep 17:5423 Sep 19:0021
181323 Sep 11:4323 Sep 18:3123 Sep 20:0019
191423 Sep 11:4623 Sep 19:2823 Sep 21:0019
201523 Sep 12:0523 Sep 12:4423 Sep 22:0022
211623 Sep 12:1923 Sep 19:2623 Sep 23:0022
221723 Sep 12:3923 Sep 14:5324 Sep 00:0024
231823 Sep 13:0523 Sep 17:0424 Sep 01:0026
241923 Sep 13:1623 Sep 15:4524 Sep 02:0029
252023 Sep 13:4423 Sep 13:4524 Sep 03:0028
262123 Sep 13:5523 Sep 19:3824 Sep 04:0028
272223 Sep 14:0423 Sep 21:4724 Sep 05:0025
282323 Sep 14:2123 Sep 20:3824 Sep 06:0023
292423 Sep 14:3923 Sep 16:2224 Sep 07:0024
302523 Sep 14:4923 Sep 16:3924 Sep 08:0026
Sheet


Actually, could be shorter:
=SUMPRODUCT(($B$6:$B$225 < E12)*($C$6:$C$225 > = E11))<e12)*($c$6:$c$225>

Copy F11 formula up and down. The data goes below what I've shown.
I've highlighted cells in column A that are eligible for including in the count as those people who were admitted before 2pm, and highlighted those in column B who were discharged after 1pm. Those with highlights in both are counted (15 in this case in F11).
Now you could chart columns E:F
<embed src="https://www.box.net/embed/pfz7tjooh29fx54.swf" wmode="opaque" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" height="400" width="466"></e12)*($c$6:$c$225>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top