Average Census

Hi,

I have data of check-in and check-out times in a hotel (date and time) and need to compile average census by hour of the day. E.g. at 1:00am the average census is 150 while at 1:00pm the average census is 80.

It is easy to create average check in times from this data, but what is difficult is factoring in the lengths of stay over multiple days.

Any ideas? I have been stumped for the last few hours and have not come up with any particularly solid ideas.

What do you mean by census?

By census I mean "total number of people checked-in at a given hour." My best guess is that the formula would be something like (date/time checked out - date/time checked in)*24. This would give total number of hours checked-in. Then I would like to use some sort of logic to add up how many people were present during each hour of the day. Finally I would divide by total number of days to get the average number of people checked in.