Daily Census by Shift

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

Hi,

I tried your formula and it still is giving me 7 for 12/2/2011.

I think I am missing something here and this could be the discharge time for this period. Is there a formula that will give me a head count for people who were admitted minus discharged for a particular date formula,

=SUMIFS($H$2:$H$5000,$A$2:$A$5000,"<="&AO3,$C$2:$C$5000,">="&AO3)

doesn't take discharge time into consideration.

My main objective is to get a census for that shift by date.

Thanks for replying to my post.

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 for my census.

The highlight in red indicates he amount of people still on
and the blue represents
the amount of people discharged.

I hope that makes sense.

Thank you again for replying to me.

Newb

Total discharge should be 3 not 4 because 12/3 is not counted for 12/2 census. For some reason it is counting this, which is not accurate.

Thanks for your help,

Newb