Excel auto Gantt chart

Question

I have a spreadsheet with 4 columns. Teachers, Class Taught, Class Start (date), Class End (date). I need to plot this in excel somehow to show what days of the month are booked by each teacher.

I1             x x x Class taught x x x
I2   x x x Class Taught x x x                     x x x Class Taught x x x
I3                x x Class x x
I4     x x Class x x     x x Class x x           x x Class x x
   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24...
               days of the month

Instructors listed on the y axis and days of the month (1 - 31) on the bottom. Graph plots a point in on each day a teacher taught a class and then labels the class. Is this sort of thing possible and what kind of graph or macro would I use.

Answer 1

You can do this in a very clunky manner by using a horizontal stacked bar chart and setting the format of the bar in the blank region of the chart to have no fill and no border so it doesn't appear. It works pretty well in simple cases where you have one or two bars with one or two gaps. Beyond this formatting the data gets very complicated because of the need for one column to per entry for each bar or gap between bars so I'm not sure it is the ideal solution in your case.

Answer 2

Here is a link to a simple GANTT CHART template that uses Conditional Formatting to color cells on a horizontal calendar. Perhaps it is adaptable to your needs.

Gantt Chart