Counting in between ranges

**dreicer_Jarr** · 04-02-2008, 05:24 PM

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.

**Portuga** · 04-02-2008, 05:36 PM

Hi,

You can use this formula to identify the time range:

=TEXT(A2,"hh")&"00-"&TEXT(A2+"1:00"+0,"hh")&"00"

I assume you can take it from here.

Sumproduct or pivot table would be my next options.

**dreicer_Jarr** · 04-02-2008, 05:51 PM

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

**dreicer_Jarr** · 04-02-2008, 06:01 PM

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?

**darkyam** · 04-02-2008, 06:23 PM

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

**Portuga** · 04-02-2008, 11:20 PM

Originally Posted by dreicer_Jarr

Make sense?

Hi,
It makes perfect sense.

Did the formula Darkyam gave you solved your problem?

I had a couple of thoughts at the time that basically involved a bit of a complex If(And(Date(year(month(day( + the time from another cell, statement.

Excel dates are just numbers formated in date format. Usually the Hours, correspond to the decimal places of those dates (if you format a date as a number with 5 or more decimals you will see what I mean).

**darkyam** · 04-03-2008, 08:52 AM

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

=SUMPRODUCT(--(FLOOR(A2:A100,1/24)<=D2)*(B2:B100>=D2))+SUMPRODUCT((A2:A100>B2:B100)*(B2:B100>=D2))

**darkyam** · 04-03-2008, 11:30 AM

Originally Posted by dreicer_Jarr

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

The formula in column AT is simply giving you the number of hours a person is in the ED. If a person is admitted at 1:30 and discharged at 2:30, that formula will return 1 hour. Based on your above post, the formula I provided counts that person as being in the ED during the 1:00 hour and during the 2:00 hour, and thus returns 2. The numbers from AT will never match the numbers in my formula unless every patient is admitted and discharged precisely on the hour.

To answer your question about the formula in detail, Floor takes a number and brings it down to the significance (1/24 or one hour in this case), so it takes the values in Q and knocks them down to the beginning of the hour. 1:34 becomes 1:00, for example. It then compares this to the date in F2 + the time in T1 and returns True or False. The second step makes sure that the discharge date is after the time in question and also returns True or False for each value in the array. Since TRUE evaluates to 1 and FALSE to 0 in Excel, Sumproduct is basically taking a string of 1s and 0s and multiplying them, then adding the results.

**dreicer_Jarr** · 04-03-2008, 01:00 PM

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?

**ellenowski** · 02-12-2014, 07:57 PM

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!

**bebo021999** · 02-12-2014, 09:19 PM

@ellenowski, Welcome to the forum. Next time try to open new thread with refer link to this topic, if needed.

Refer to your formula, I don't think you can enter after because of the warning message " the formula contains error". Your IF function has logiccal_test only, no value_if_true
and no value_if_false.

Besides, IF is not the good choice for this case. I belive I can figure it out if:

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to
show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**ellenowski** · 02-12-2014, 11:37 PM

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

**FDibbins** · 02-13-2014, 12:54 AM

ellenowski, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.