daily income based on date, admission, discharge and fee on a given date

Any help gratefully received.
I am trying to calculate the bed occupancy rates per care home for each day. [fees are weekly values that's why they need to be divided by 7]

I have the patient name, admission date and discharge date.
Fees change from time to time that's why I want to add columns (E:onwards) each time fee changes and entering other residents fees to match their current state.[ unless there's an easier way]

I tried to use various formulas but just got a headache.
On Sheet Residency i have weeks which i'll be copying down.
I need to calculate how many residents of a given fee were in residency on a given date[column C Residency sheet]
Is there some way of automatically calculating how many residents of a given fee were in Branch court care home based on sheet BC?
Thanks in advance.

Let me know if you need further explanations - i will be extremely grateful for any help and hints.[workbook attached]:confused::confused:

A