Gantt, timeline chart, multiple bars on the row, line ?

How can I make a Gantt, timeline chart but with multiple bars on the same row, in Excel?

I want to make an Excel chart that displays the intervals of time a certain room of a hotel is occupied (somebody pays for it). The chart should look like the one which I tried to draw below where "#####" is a black bar and "____" an empty white space.
Y axis have to contain the rooms ordered from 1 to n and X axis the years and months or only the years.

I have the date the client (various clients) went into each room and also the date when he or she left the hotel.

Room 1 | #####____######_________###########

Room 2 | ____########_____#########_____#################

Room 3 | ######__#######_______________###########

Room 4 | #####_____#########___#####__####__####___####


QUESTION: How can I build, display such a chart in Excel starting from "Check-In" and "Check-Out" data that I have for each room?

Unfortunately its not an easy option, here is what I came up with. The problem is that because I use a formula, there are not any gaps, if you delete the formula in the cells where it is blank the gap actually appears. Graphs are kind of annoying like that.

Thank you for your answer.
Can you save that file "Gantt Test.xlsx" in xls format and post it again, because I have Excel 2002 and I can not open it.

Not a problem, I think these equations work in 2003.

OK, I have opened it.
Your idea is interesting and it inspired me to build a bar chart (see the attached file). Unfortunately, I also have to modified the chart by hand and also this bar chart suffers from other problems like displaying % on the X axis.

Maybe there is a way in VBA to quickly and automatically change the settings of the chart and transform it from a multicolor diagram to something that looks like the drawing in my first post.