Hello - Thank you for taking the time out to view my post.

I want to calculate the avg daily census through admit and discharge dates. Furthermore, I would like to see this by shift.

I am attaching my spreadsheet with all the admit dates/discharge dates and times.
testcensus.xlsx

I have three shifts: Day/PM/Night

I am using the following formula for day admits
=IF(AND(B3>=--"7:00",B3<=--"15:30"),"1","0")

and following for day discharge
=IF(AND(D3>=--"7:00",D3<=--"15:30"),"1","0")

---I am using CONCATENATE function to pull data from previous dates, but it is not giving me my daily census per shift.

For example,
if there were 5 admits on 12/2
and
4 admits on 12/1 that were also there on 12/2
and out of those 4, 3 were discharged on 12/2

I should get 6 as my census for that particular shift.

I highlighted this in blue for discharge and red for people who stayed on for Day shift.

testcensus.xlsx

Thank you again for all your help.

Newb