problems calculating occupancy

[Mike952]

I am going round in circles with this one.

In essence I need to count the number of people in the department on a specified day, at any time, by the length of time they have been there.

The info from the attached will be pulled into another table by referencing the discharge date and counting the numbers of yes in each time period and the LOS from column AC.

This separate table which will drive a stacked Bar chart.

The first bit is getting the formulas in columns E to AB to work.

Each line represents 1 persons stay, so I need to see a YES in all the time blocks that they occupy, which is not happening.

Its an issue because they span over 24 hrs, i.e. some people who came in yesterday are still here today and some people who came in today, will not go home till tomorrow.

Making sense so far?

Could you please look at the If statements I am using as they are not working.

I have tried IF(AND as well as IF(OR, but I am clearly getting the logic wrong.

Any help appreciated.