Gnatt chart based on a time schedule

Occasional Visitor

I need to create a chart based on a list of courses and their scheduled time. I am having trouble starting this. Right now we manually color in the course blocks based on the course times each day. However, I want to be able to just input the the new course offering schedule each term and the chart auto update.


Here is a sample class schedule. 



So the goal would be that on Monday, the 3 course offerings would color-fill another sheet and essentially create a blocked summary. Below is what we created manually.




I tried creating a Gnatt chart with the if(and formula searching for if a time falls between the start and end times, but that seems to only account for one course. How can I get this started to reflect all courses (possibly 50 +). Ignoring duplicates because two+ courses can have the same time offerings, but we only need that entire section blocked.


Another thing I am having trouble with logically is that if a situation like below appears.

Monday: Class A is from 9 AM to 10 AM, and Class B is from 9 AM to 10:30 AM. In this instance the full block should be from 9 AM to 10:30 AM (the longest timeframe).


Any starting points would be helpful. 



1 Reply

@ChristinaN9  I didn't use the chart but instead used some formulas to fill in the chart with the time if a class exists at that time and blank otherwise. Then I use 2 conditional formatting rules: one to color all cells with time values in them and the other to make the text of all cells with time above and below match the fill color so you get the effect of your image.  

See attached.

BTW, although not required, I did make the course listing sheet a table and used table references and named the range of times in column A "times" to make it easier/cleaner.