Calculating Daily Census

ExcelNerd24 · Dec 6, 2024

Hello,

What formula could I use to calculate the daily census based on the admission and discharge dates noted below?

Dave Patton · Dec 6, 2024

I cannot use an image to create an example with your data.
Try the following

T202412a.xlsm

C

F

G

H

I

J

K

1

Name

Admission

Discharge

Daily Census

2

3

A

1-Nov-24

2

1-Nov-24

1

4

B

5-Nov-24

20-Nov-24

2-Nov-24

1

5

C

20-Nov-24

1-Dec-24

3-Nov-24

1

6

D

1-Dec-24

4-Nov-24

1

7

5-Nov-24

2

8

6-Nov-24

2

9

7-Nov-24

2

10

8-Nov-24

2

11

9-Nov-24

2

12

10-Nov-24

2

13

11-Nov-24

2

14

12-Nov-24

2

15

1d

Cell Formulas
Range	Formula
I3	I3	=COUNTIFS(F:F,">0")-COUNTIFS(G:G,">0")
K3:K14	K3	=SUM((F:F<=J3)(F:F>0),-(G:G<=J3)(G:G>0))

Peter_SSs · Dec 7, 2024

@ExcelNerd24
Welcome to the MrExcel board!

I note that you have "Liked" post 2. If you are going to use that formula structure then I would highly recommend changing from whole column references to something much smaller but still big enough to cover any data you might have. In the mini sheet* below I have used that formula in column K and an alternative in column L. On my machine just those 20 formulas in column K take over 3 seconds to recalculate whereas the 20 formulas in column L take about 0.005 seconds to recalculate. I'm not suggesting that is because of the different formula (that is just an option) but mainly because of the restricting the rows to 1000 instead of over 1 million.

* I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

24 12 07.xlsm