Calculating # of Patients in process by Hour of Day

[jsolofly]

Hello,

I'm trying to figure out a way that I can calculate the # of patients that were in process (at the hospital) by hour of day (0-24). I have a unique account, # the start and depart times for all patients, but can't seem to wrap my mind around counting the # of patients that were here over many hours. For example lets say Patient A was here from 3pm to 8pm. This patient would be included in hours 15-20.

I ultimatley would like to have the output look like this:

Hour of Day: 0 1 2 3 4 5 6 7 8 9 10 .......
# of Cases in Process: 5 2 7 10 12 15 8 13 11 ........

Any suggestions would be greatly appreciated.

Thanks.

[Paul]

Hi Jsolofly, welcome to the forum.

Might I suggest you upload a sample workbook that has the same layout and data type as your actual workbook? Include fake data of the same type where it would normally be on your workbook, and show what results you would expect and where. I'm sure we could create a formula for you without it, but then it might take another 10 posts to get it to work in your actual layout.

[jsolofly]

Not a problem. I'm attaching simple workbook that gives a good idea of what I'm working with and what I would like to get as a result. Thanks for the help and please let me know if there is anything else I can provide that would be helpful.

[Russell Dawson]

See next post

[Russell Dawson]

Updated workbook

[jsolofly]

Russell Dawson,

Thanks you so much for your help. As you stated it looks like this works perfect if the Arrival Hour is before the Depart Hour, but gets lost when it is the other way around (ex. arrived hour 22 departed hour 3). Is there any way around this?

Thanks again for the help.

[Russell Dawson]

Depends upon the way it's going to be used. As you have it it looks like it's meant to be just a page per day to be reused. You could add extra days to enable you to slip over the 24 hour mark but if what you want is to record an hour by hour count then it should work. It's at the start and end of the day that could be problematical but if, say someone starts in the last hour then 24 is input. If they depart in the same hour then 24 is input and you have a count for that hour. Similarly, if start is after midnight and departs in same hour the same applies. You do say that it is meant to be a record of patients that were in process and it doesn't intend to be a dynamic s/s that is updated as time proceeds so it should be sufficient for your needs. I know it's not perfect and I will give it more thought (another sleepless night ahead!) If I think of anything I'll get back to you. Anyway, who would be discharging patients around midnight? Ah - Emergency room or whatever you guys call it!

Cheers

Russell

[Russell Dawson]

Try this. I've not tested to destruction but you will see that it allows for the move into the next 24 hour period. If you want to create a week then that can be your project for the weekend!

[jsolofly]

Hi Russell,

This is extremely helpful. I appreciate your time and effort in helping come up with a solution for this. I'll take if from here and will expand it to a week. Thanks again.