Counting in between ranges

I am trying to count the number of patients that were in a hospital during specific times (time and date). I want to know, by hour, how many patients are in the hospital. I am using a month of data as my "sample size".

I all the start times (e.g. 3/1/2008 12:35:00 AM) and leave times (e.g. 3/1/2008 5:52:00 AM) and I want to have the patients by hour for the month. So I want to know that during, as an example, 2:00:00 AM to 3:00:00AM there were 40 patients in the hospital.

So I will eventually combine all one hour periods (e.g. 2:00:00 AM to 3:00:00 AM) for all the days, but it's counting (patients per hour) where I am confused.

I am very good with Excel. Please make me excellent.

So what which time/date would be linked to A2

I want to count one time for every hour a patient is here....so have two collums to deal with....more help?

Arrival ED Discharge
3/1/2008 0:35 3/1/2008 5:52
3/1/2008 2:00 3/1/2008 3:15
3/1/2008 2:14 3/1/2008 4:00
3/1/2008 2:20 3/1/2008 6:10
3/1/2008 2:45 3/1/2008 5:30
3/1/2008 2:49 3/1/2008 5:40
3/1/2008 3:55 3/1/2008 7:18
3/1/2008 4:05 3/1/2008 8:16
3/1/2008 6:00 3/1/2008 8:03
3/1/2008 6:18 3/1/2008 10:55
3/1/2008 6:30 3/1/2008 10:44
3/1/2008 7:35 3/1/2008 8:50

I see what you're saying, but I need a count for all hours between the times (one patient per hour)....so if a patient were here from 1:13 am to 4:45 am, I would need that patient counted once for each hour he/she was here

1:00 - 2:00 = 1
2:00 - 3:00 = 1
and so on

I then want to get a total census for all the dates and times people are in the hospital. So it would look like this

1:00 - 2:00 = 83
2:00 - 3:00 = 68

based on the collumns displayed above. Make sense?

Try =SUMPRODUCT(--(FLOOR(A2:A100,1/24)<=D2)*(B2:B100>=D2)), assuming 1:00 is written in D2.

Unless no one stays overnight, I would imagine my solution was incomplete. Try

Please Login or Register  to view this content.

You're on the money, but I wasn't clear enough. Because the data is formatted as date and time (like this)

One Collumn
3/1/2008 12:35:00 AM
3/3/2008 4:22:00 PM
3/7/2008 2:35:00 AM

2nd corresponding Collumn
3/1/2008 2:58:00 AM
3/3/2008 8:40:00 PM
3/7/2008 3:56:00 AM

I think we'll need a different formula. I jumped to the end result too quick. Before then, we'll need by date and hour....then I am going to group all similar days of the month....but I can do that. I REALLY APPRECIATE BOTH YOUR HELP!!

I could be wrong, but I think that makes my first formula valid. You would just type the date as well as the time into D2.

Here's a shrunk down file....

Please be more specific in the future about where a proposed solution is providing inaccurate or unexpected results, especially if not immediately obvious. It took me a few minutes to realize that the issue occurred in column BC. The root of the problem is in column S, which was reading 12:00AM, not 12:00PM. You can change the formula in AQ2 to be =SUMPRODUCT(--(FLOOR($AN$2:$AN$31,1/24)<=$F2+AQ$1)*($AO$2:$AO$31>$F2+AQ$1)) and drag across and down, then delete columns G:AD unless they serve some other purpose. Alternatively, just change the time in column S to be 12PM.

I will try and be more specific. I have taken your formula, but when I go to validate the data, I am not accounting for every hour that a patient is in the ED. This is making me fear that the formula is incorrect. Because I am not familiar with this formula, it's tough for me to tell what is wrong.

I have a feeling that the formula is not asking to count one for every hour a patient is in the ED based on their specific times. In laments terms, what is the formula asking to do?

I have to run to a meeting, but will check back ASAP. I can't thank you enough. See if you can verify (see attached). The cells in yellow should match; I think.

J

Amazing. You are great. My one last concern is whether the formula should be >= or > and much the same on the second part <= or <? If a patient was there on the hour they should be counted in that hour, so 12:34 AM to 3:00 AM should have a patient census of 1 at 12:00, 1 at 1:00, 1 at 2:00, AND 1 at 3:00....so which way should the signs be pointed?

I noticed when I repeated this analysis and did not delete the fields where there was a blank for time/date on one of the collums (primarily arrival) the numbers were not the same....and seemed way too high to be accurate.

Why would this be?

The arrows would be <= and >= in that case.
For your last post, a blank is read as 0, so all times on your chart are going to be after that "date", making the first part of the formula meaningless. It would count 1 for every hour until that person left the ED. If the departure was blank, the formula would return 0 for every hour because the second half of the formula will always read that the person was discharged before any of the times on your chart.

I realize this is an older thread, but it is very closely related to my problem and I am hoping someone can help. I am trying to count how many employees are clocked in during a specified hour based on punch clock data to provide data for updating the schedules by manager. Don't want just a total count by hour, need it very granular in order ti pivot/report off of the data.

The problem I have is that the data varies by employee. Some have 1 punch in, 1 out. Some have 2-3 punches in/out. Some have 2 in, 1 out. I am trying to run a formula on each row of data which represents one day of punches for a specific employee, and have it give me an "X" or a 1 in a column corresponding with each hour of the day. Here's what I have so far:

A / B / C / D / E / F / G / H / I / J / K / L / M / N / ...
1 Name / Time 1 / Label 1 / Time 2 / Label 2 / Time 3 / Label 3 / Time 4 / Label 4 / Time 5 / Label 5 / Time 6 / Label 6 / 0 / 1 / 2 / 3 / 4 / ... / 23
2 Emp A / 6:57 / In / 11:38 / Out / 15:47 / In
3 Emp B / 9:53 / In / 14:01 / Out / 14:54 / Meal / 19:05 / Out
4 Emp C / 6:02 / In / 10:57 / Out / 11:44 / Meal / 14:47 / Out / 18:58 / In
5 Emp D / 7:22 / In / 12:01 / Out / 12:52 / Meal / 16:29 / Out / 16:45 / In / 18:09 / Out

In N2, I have the following formula: =IF(OR(AND(N$1>=HOUR($B2),N$1<=HOUR($D2)),AND(N$1>=HOUR($F2),N$1<=HOUR($H2)),AND(N$1>=HOUR($J2),N$1<=HOUR($L3)))

I get a #Value error when there isn't a time listed in the referenced cell. So for example, in row 2 above, I would get a #Value error because there is no value in columns H, J, and L. In this instance, I would want it to give me an X in the columns corresponding to hours 6, 7, 8, 9, 10, 11, and then 15, but nothing else since I don't have a punch out time to close on.

I tried all of the solutions provided above, and they don't work for my problem. I don't know how to get there without either an unwieldy formula or an array, and either way, I am flummoxed. Please help!

Thank you for the support. Here is a sample of my worksheet. I think I hadn't copied the whole formula when I pasted it in the post. I did discover that there was data in the cells even though they were displaying as empty. Once I cleared the cells, it did give me some results, but it still isn't quite right. Don't mind ditching the IF formula for something that works.

Punch Clock hourly count.xlsx