Using Excel to calculate occupancy

[Nad4]

Hi, I have a spreadsheet with arrival times and discharge times for thousands of patient attendances. Using the arrival time and discharge time I am looking to calculate the number of people in the department by hour so the patient numbers at 00, 01, 02 etc through to 23. For example if a patient arrived at 01:10 and was discharged at 05:20 they would be in the department at 01, 02, 03, 04, 05. Is there an easy way to calculate this for each of the 24 hours?

 

[awoohaw]

Welcome to the forum. Would you be able to provide a sample (sanitized for privacy) set of data? It takes time for the forum to completely build your scenario.
The forum has a tool called xl2bb add in (link below) that allows you to easily copy and paste ranges from worksheets. If you cannot do that, then please post your data as a table (copy/paste). While images are helpful, it still means the forum needs to recreate entirely.

Thanks in advance.

 

[awoohaw]

Here is a suggestion with some made up data:

Mr Excel Questions 75.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Check in	Check Out		9:00 AM	10:00 AM	11:00 AM	12:00 PM	1:00 PM	2:00 PM	3:00 PM	4:00 PM	5:00 PM
2	09:00	10:43		50	80	78	59	33	8	0	0	0
3	09:00	12:08
4	09:03	11:34
5	09:03	12:33
6	09:03	11:20
7	09:04	13:51
8	09:04	11:19
9	09:05	11:26
10	09:08	11:46
11	09:08	12:48
12	09:10	12:44
13	09:10	10:24
14	09:12	12:57
15	09:12	13:12
16	09:12	11:20
17	09:12	11:34
18	09:12	13:02
19	09:12	13:03
20	09:14	11:08
21	09:15	12:50
22	09:15	10:46
23	09:15	10:42
24	09:16	13:11
25	09:18	12:53
26	09:20	11:08
27	09:21	10:22
28	09:22	11:57
29	09:23	12:49
30	09:24	13:08
31	09:28	14:05
32	09:30	12:20
33	09:31	10:57
34	09:33	12:16
35	09:34	12:19
36	09:35	14:01
37	09:36	12:17
38	09:36	10:40
39	09:39	11:32
40	09:40	13:42
41	09:44	11:27
42	09:50	13:45
43	09:50	13:25
44	09:50	11:22
45	09:51	10:45
46	09:54	13:08
47	09:56	12:05
48	09:56	12:39
49	09:56	13:05
50	09:57	12:17
51	09:59	13:37
52	10:06	13:04
53	10:06	14:07
54	10:10	11:19
55	10:14	13:36
56	10:18	12:31
57	10:18	11:58
58	10:20	13:15
59	10:20	11:00
60	10:21	11:22
61	10:21	13:02
62	10:25	11:03
63	10:28	12:37
64	10:28	13:38
65	10:35	11:01
66	10:39	13:08
67	10:40	14:17
68	10:42	10:55
69	10:44	14:03
70	10:44	12:56
71	10:44	10:39
72	10:46	13:29
73	10:46	13:36
74	10:48	12:58
75	10:48	12:33
76	10:52	14:01
77	10:54	13:53
78	10:56	12:36
79	10:56	11:56
80	10:56	12:42
81	10:58	11:35
82	11:00	10:27
83	11:09	10:27
84	11:10	11:05
85	11:24	12:10
86	11:39	10:37
87	11:40	10:52
88	11:42	11:32
89	11:48	11:47
90	11:54	14:01
91	11:56	14:05
92	11:56	13:02
93	11:57	12:06
94	12:15	12:52
95	12:24	13:36
96	12:28	10:28
97	12:30	13:56
98	12:40	12:54
99	12:48	12:05
100	13:30	10:42
101	13:55	13:08
102
Sheet9

Cell Formulas
Range	Formula
D1:L1	D1	=TIME(SEQUENCE(1,9,9,1),0,0)
D2:L2	D2	=SUM((HOUR(D$1)>=HOUR($A2:$A101))*(HOUR(D$1)<=HOUR($B2:$B101)))
Dynamic array formulas.

 

[Nad4]

Thank you for this, I will try this and let you know. Thanks again.