Creating a timeline that shows when a classroom is scheduled to be occupied.

[snellington]

Hello Excel people,

I work in higher education and have been tasked with creating a timeline of sorts that shows when a room has class and when it does not to help see if there are any gaps that could be filled with other classes.

My data is laid out as follows:

Room	Start	End	Duration	Mon	Tue	Wed	Thur	Fri
BUS111	800	850	50	X		X		X
HOH312	900	1015	115		X		X
BUS111	1000	1050	50	X		X		X
etc for 2000 rows

<tbody>
</tbody>

I first started by creating a Gantt Chart, which worked to a degree, the only problem is it doesn't account for the room being listed multiple times (as shown in table above with BUS111). It treated them as individual values and listed it twice. (The raw data has some classes listed 30+ times).

Any advice would be greatly appreciated. I can provide the raw data if necessary... someone will have to tell me how though.

 

[Smitty]

Welcome to the Board!

If you follow the HTML Maker link in my sig you'll be able to post a screen shot(s).

 

[snellington]

Excel 2010

	A	B	C	D	E	F	G	H	I
1	Start	End Time	Duration	Bldg+Room	Monday Ind	Tuesday Ind	Wednesday Ind	Thursday Ind	Friday Ind
2	1400	1515	115	MCS 103	M		W
3	800	915	115	CTL 117		T		R
4	1100	1215	115	MCS 105		T		R
5	1000	1050	50	BUS 113	M		W		F
6	1100	1215	115	BUS 113		T		R
7	930	1045	115	BUS 211		T		R
8	900	950	50	BUS 209	M		W		F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Raw Data​

Here are the first 8 lines of data. Thanks!