Given start and stop times, average room occupency

[eddelmon]

Initially I posted this in formulas but after sleeping on it realized that something in VBA may be more appropriate. What I have is times a room was occupied, start and stop time and room number. What I need is average total room occupancy by hour throughout the day. For instance at 8:00am I only expect somewhere between 2-5 rooms to be occupied but at 8:00PM I expect around 25 or so to be occupied. For purposes of this project if the room was occupied for one minute during the hour say 8:59 it counts as being occupied.

At the bottom is a sample of my data. I have 27 rooms (30-56) and I need to determine whether each room was occupied during a particular hour. For instance if you note, on 4/1 room 30 was occupied during the 8 o'clock, 9 o'clock and 10 o'clock hour but was empty for the 11 o'clock hour and was then again occupied during the 12 o'clock hour. Two attachments: One of a small sample, one with all data.

Hoped for result would look something like this (totally made up results)

100am 20
200am 10
300am 6
400am 0
500am 0
600am 0
700am 0
800am 4
900am 7
1000am 15
1100am 20
1200am 22
100pm 24
200 pm 23
etc


I'm very open to any other suggestions for calculating it. Thanks so much for any assistance

Thanks so much.

[Glenn Kennedy]

Amused to speculate on the nature of the business where rooms occupancy changes by the hour!! RoFLAO...

[eddelmon]

Hospital emergency department =p

[Glenn Kennedy]

Quick thinking there, Sir!! I'm away now. will look back in EU morning.

[eddelmon]

Thanks so much, I appreciate it.

Believe it or not its true. We're trying to make a case to management for more staff.