Counting cumulative occupancy based on arrival date and length of stay

**HarveyDickinson** · 09-10-2009, 07:19 AM

I need to work out the number of rooms occupied per day; the information I have is the date a person arrives and how long they stay. I suppose possibly it could be done by adding 1 to the daily total for a number of days equal to their length of stay? Really I have no idea how to do it!
So far I've been doing it with a chart, with 'room number' in the rows and 'date' in the columns - so if a person comes in on day 1 for 5 days I mark 5 cell in a row, then if a person comes in on day 2 for 10 days I mark 10 cells in the next row. Then I simply use COUNTA in the column to work out the total.
Can anyone either think of a way to automate the 'chart' method, or preferably some kind of formula to just give the numbers!

Thanks,

Harvey

**HarveyDickinson** · 09-10-2009, 08:02 AM

Sorry! I changed it, is that better?

**arthurbr** · 09-10-2009, 08:40 AM

Thx for changing.The aim is to give users the possibility to search on keywords, which are usually terms used in XL (array, sum, etc...)
Anyway, thx for the effort

**arthurbr** · 09-10-2009, 08:42 AM

To make things easier, could you post a small sample of your data, AND what you are trying to achieve ? Thx

**HarveyDickinson** · 09-10-2009, 08:58 AM

Ok...this is the first week's data that I have.
Each cell represents a person, while the number in each box represents their length of stay. I want to calculate the rooms occupied each day.
Clearly on 1st April the number of rooms occupied is 23 - the same as the number of people coming. On 2nd April however, new people come in and some people from the previous day leave. 15 people (all those with length of stay more than 1) will still be in a room from the previous day, plus all admissions on the 2nd.
Does that make sense?