Creating a daily census count

[Mrocks013]

Preface: This is my first post.

I am attempting to create a daily census table for a 12 bed facility based on client admission and discharge dates.
The date of discharge should not be counted.

Book1
	A	B	C
1	Client Name	Admission Date	Discharge Date
2	Margaret	7/30/2015	8/6/2015
3	Derek	7/30/2015	8/5/2015
4	Leslie	7/30/2015	7/31/2015
5	Ian	7/30/2015	7/31/2015
6	James	7/31/2015	8/5/2015
7	Kathleen	8/2/2015	8/6/2015
8	Pieter	8/2/2015	8/7/2015
9	Matthew	8/3/2015	8/8/2015
10	Danielle	8/3/2015	8/8/2015
11	Ernesto	8/3/2015	8/10/2015
12	Devon	8/4/2015	8/5/2015
13	Robert	8/4/2015	8/10/2015
14	Erik	8/5/2015	8/7/2015
15	Curtis	8/5/2015	8/10/2015
16	Perri	8/5/2015	8/11/2015
17	Daniel	8/5/2015	8/7/2015
18	Brittany	8/6/2015	8/11/2015
19	Kerry	8/6/2015	8/10/2015
20	Ryli	8/7/2015	8/13/2015
21	Meaghan	8/8/2015	8/12/2015
22	Cory	8/8/2015	8/12/2015
23	Charles	8/8/2015	8/12/2015
24	Michael	8/10/2015	8/17/2015
25	John	8/10/2015	8/17/2015
26	Kimberly	8/10/2015	8/15/2015
27	Christopher	8/12/2015	8/14/2015
28	Justin	8/12/2015	8/12/2015
29	Lisa	8/12/2015	8/18/2015
30	andrew	8/12/2015	8/17/2015
31	Jacquelin	8/12/2015	8/17/2015
32	Kathyleen	8/13/2015	8/19/2015
33	Steven	8/13/2015	8/19/2015
34	Laurie	8/14/2015	8/19/2015
35	Elias	8/14/2015	8/18/2015
36	Devon	8/15/2015	8/23/2015
37	Zachary	8/16/2015	8/24/2015
38	John	8/17/2015	8/21/2015
39	Larry	8/17/2015	8/23/2015
40	Shane	8/17/2015	8/24/2015
41	Richard	8/18/2015	8/24/2015
42	Katherine	8/18/2015	8/20/2015
43	Alex	8/18/2015	8/22/2015
44	Adam	8/19/2015	8/27/2015
45	Tessa	8/20/2015	8/26/2015
46	Terri	8/21/2015	8/27/2015
47	Matt	8/21/2015	8/27/2015
48	Thomas	8/22/2015	8/22/2015
49	Steven	8/22/2015	8/24/2015
50	Austin	8/23/2015	8/31/2015
Sheet1

 

[DRSteele]

Welcome to the forum.

Exactly what are you trying to achieve? Do you wish to count the number of persons admitted per date?

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean?

E3 and F2 copied down.

Excel Workbook
	A	B	C	D	E	F
1	Client Name	Admission Date	Discharge Date		Date	Count
2	Margaret	30/7/2015	6/8/2015		30/7/2015	4
3	Derek	30/7/2015	5/8/2015		31/7/2015	3
4	Leslie	30/7/2015	31/7/2015		1/8/2015	3
5	Ian	30/7/2015	31/7/2015		2/8/2015	5
6	James	31/7/2015	5/8/2015		3/8/2015	8
7	Kathleen	2/8/2015	6/8/2015		4/8/2015	10
8	Pieter	2/8/2015	7/8/2015		5/8/2015	11
9	Matthew	3/8/2015	8/8/2015		6/8/2015	11
10	Danielle	3/8/2015	8/8/2015		7/8/2015	9
11	Ernesto	3/8/2015	10/8/2015		8/8/2015	10
12	Devon	4/8/2015	5/8/2015		9/8/2015	10
13	Robert	4/8/2015	10/8/2015		10/8/2015	9
14	Erik	5/8/2015	7/8/2015		11/8/2015	7
15	Curtis	5/8/2015	10/8/2015		12/8/2015	8
16	Perri	5/8/2015	11/8/2015		13/8/2015	9
17	Daniel	5/8/2015	7/8/2015		14/8/2015	10
18	Brittany	6/8/2015	11/8/2015		15/8/2015	10
19	Kerry	6/8/2015	10/8/2015		16/8/2015	11
20	Ryli	7/8/2015	13/8/2015		17/8/2015	10
21	Meaghan	8/8/2015	12/8/2015		18/8/2015	11
22	Cory	8/8/2015	12/8/2015		19/8/2015	9
23	Charles	8/8/2015	12/8/2015		20/8/2015	9
24	Michael	10/8/2015	17/8/2015		21/8/2015	10
25	John	10/8/2015	17/8/2015		22/8/2015	10
26	Kimberly	10/8/2015	15/8/2015		23/8/2015	9
27	Christopher	12/8/2015	14/8/2015		24/8/2015	5
28	Justin	12/8/2015	12/8/2015		25/8/2015	5
29	Lisa	12/8/2015	18/8/2015		26/8/2015	4
30	andrew	12/8/2015	17/8/2015		27/8/2015	1
31	Jacquelin	12/8/2015	17/8/2015		28/8/2015	1
32	Kathyleen	13/8/2015	19/8/2015		29/8/2015	1
33	Steven	13/8/2015	19/8/2015		30/8/2015	1
34	Laurie	14/8/2015	19/8/2015		31/8/2015	0
35	Elias	14/8/2015	18/8/2015
36	Devon	15/8/2015	23/8/2015
37	Zachary	16/8/2015	24/8/2015
38	John	17/8/2015	21/8/2015
39	Larry	17/8/2015	23/8/2015
40	Shane	17/8/2015	24/8/2015
41	Richard	18/8/2015	24/8/2015
42	Katherine	18/8/2015	20/8/2015
43	Alex	18/8/2015	22/8/2015
44	Adam	19/8/2015	27/8/2015
45	Tessa	20/8/2015	26/8/2015
46	Terri	21/8/2015	27/8/2015
47	Matt	21/8/2015	27/8/2015
48	Thomas	22/8/2015	22/8/2015
49	Steven	22/8/2015	24/8/2015
50	Austin	23/8/2015	31/8/2015
Census

 

[Mrocks013]

Thank you both for responding. Peter hit it on the head! Thank you very much!!

 

[Peter_SSs]

Thank you both for responding. Peter hit it on the head! Thank you very much!!

You are very welcome. Glad it's what you wanted.

(For any future questions, try to keep the sample data a bit smaller, so long as it is still representative of the issues. Keeps the posts & thread a bit easier to read & navigate.)

 

[mihai6900]

I have a similar problem that I need help with. I have a list of ventilated patients for which I know the start and end dates - see below. I am trying to figure out how I can calculate the average number of ventilated patients during a particular day of the month (How many ventilated patients we had on 2/5/16 for example). Is there some calculation that can help with this?

Thank you.

[TABLE="width: 205"]
<tbody>[TR]
[TD]Procedure/Treatment Start Date[/TD]
[TD]Procedure/Treatment Stop Date[/TD]
[/TR]
[TR]
[TD]2/2/16 12:08 PM[/TD]
[TD]2/2/16 7:50 PM[/TD]
[/TR]
[TR]
[TD]2/2/16 8:00 PM[/TD]
[TD]2/4/16 2:30 PM[/TD]
[/TR]
[TR]
[TD]2/3/16 2:25 AM[/TD]
[TD]2/5/16 12:44 AM[/TD]
[/TR]
[TR]
[TD]2/3/16 8:45 AM[/TD]
[TD]2/3/16 12:20 PM[/TD]
[/TR]
[TR]
[TD]2/3/16 12:30 PM[/TD]
[TD]2/10/16 4:50 PM[/TD]
[/TR]
[TR]
[TD]2/3/16 9:42 PM[/TD]
[TD]2/4/16 4:30 PM[/TD]
[/TR]
[TR]
[TD]2/4/16 5:00 AM[/TD]
[TD]2/5/16 7:46 AM[/TD]
[/TR]
[TR]
[TD]2/5/16 3:31 PM[/TD]
[TD]2/6/16 12:40 PM[/TD]
[/TR]
[TR]
[TD]2/5/16 10:10 PM[/TD]
[TD]2/12/16 9:00 AM[/TD]
[/TR]
[TR]
[TD]2/6/16 12:17 AM[/TD]
[TD]2/6/16 5:00 PM[/TD]
[/TR]
[TR]
[TD]2/6/16 3:38 PM[/TD]
[TD]2/7/16 12:15 PM[/TD]
[/TR]
[TR]
[TD]2/7/16 2:45 AM[/TD]
[TD]2/7/16 6:06 AM[/TD]
[/TR]
[TR]
[TD]2/7/16 1:13 PM[/TD]
[TD]2/12/16 10:30 AM[/TD]
[/TR]
[TR]
[TD]2/8/16 5:01 AM[/TD]
[TD]2/11/16 2:55 PM[/TD]
[/TR]
[TR]
[TD]2/8/16 8:00 AM[/TD]
[TD]2/22/16 10:35 PM[/TD]
[/TR]
[TR]
[TD]2/8/16 11:00 PM[/TD]
[TD]3/11/16 1:35 PM[/TD]
[/TR]
[TR]
[TD]2/8/16 11:38 PM[/TD]
[TD]2/9/16 4:15 PM[/TD]
[/TR]
[TR]
[TD]2/9/16 9:30 AM[/TD]
[TD]2/10/16 2:47 PM[/TD]
[/TR]
[TR]
[TD]2/9/16 4:37 PM[/TD]
[TD]2/12/16 12:30 PM[/TD]
[/TR]
</tbody><colgroup><col span="2"></colgroup>[/TABLE]

 

[Peter_SSs]

Your question isn't clear to me. You mentioned an average but then gave a specific date.

This should give you an actual count of patients whose treatment dates included at least part of the date in question. In this example, it is the yellow rows that have been counted.
Note that my dates are in d/mm/yyyy format.

Post back with more details, including the expected result, if this is not what you wanted.

Excel Workbook
	A	B	C	D	E
2	2/02/2016 12:08 PM	2/02/2016 07:50 PM		Date	5/02/2016
3	2/02/2016 08:00 PM	4/02/2016 02:30 PM		Count	5
4	3/02/2016 02:25 AM	5/02/2016 12:44 AM
5	3/02/2016 08:45 AM	3/02/2016 12:20 PM
6	3/02/2016 12:30 PM	10/02/2016 04:50 PM
7	3/02/2016 09:42 PM	4/02/2016 04:30 PM
8	4/02/2016 05:00 AM	5/02/2016 07:46 AM
9	5/02/2016 03:31 PM	6/02/2016 12:40 PM
10	5/02/2016 10:10 PM	12/02/2016 09:00 AM
11	6/02/2016 12:17 AM	6/02/2016 05:00 PM
12	6/02/2016 03:38 PM	7/02/2016 12:15 PM
13	7/02/2016 02:45 AM	7/02/2016 06:06 AM
14	7/02/2016 01:13 PM	12/02/2016 10:30 AM
15	8/02/2016 05:01 AM	11/02/2016 02:55 PM
16	8/02/2016 08:00 AM	22/02/2016 10:35 PM
17	8/02/2016 11:00 PM	11/03/2016 01:35 PM
18	8/02/2016 11:38 PM	9/02/2016 04:15 PM
19	9/02/2016 09:30 AM	10/02/2016 02:47 PM
20	9/02/2016 04:37 PM	12/02/2016 12:30 PM
Count