Leno and Conan Excel Gantt Chart

Rumours say that the late night TV schedule on NBC will change. Jay Leno will leave his 10 PM spot, and return to 11:35 PM. How long will the revised Leno show be, and what effect will it have on the rest of the schedule?
I’ll bet the NBC programming executives have set up an Excel worksheet to test the possible scenarios, and they made a nice Gantt chart to show the results.

The Late Night Schedules

Just in case those executives need a bit of help, I set up a sample worksheet for them. At the top, I entered a list of the current shows, with their duration in hours and minutes.
I entered Jan 1, 2010 10:00 PM as the start date and time for the first show. It doesn’t matter what date you use, but you need to include one so the chart will work correctly. Then, the Current Start column is formatted to show just the time, not the date.
Start times for the remaining shows are calculated, based on the previous show’s start time and duration. For example, in cell B3, the formula is =B2+C2
Based on the current scheduling rumours, I created a list of the revised late night lineup. Again, the durations and first show’s date and start time are typed in. Remaining start times are calculated.

Create a Gantt Chart

To visually compare the timelines, I wanted to create a couple of Gantt charts. The hardest part about making a Gantt chart in Excel is remembering how to spell Gantt. Gaant? Gannt? Gaanntt? That’s why I like Line charts better – they’re easier to spell.
Here are the steps that I followed to create the Gantt chart for the current schedule.

  1. Select a cell in the current schedule table.
  2. On the Ribbon’s Insert tab, click Bar, then click Stacked Bar
    • Stacked Bar Chart
  3. In the bar chart, click the first series, Current Start, and change its fill colour to No Fill. That hides the first series, so you can focus on the duration of each show.
    • LateNight03

Format the Vertical Axis

Next, you can change the order in which the shows are listed, so the earliest show is at the top of the chart.

  1. Click on one of the show names in the chart, to select the vertical axis.
  2. On the Ribbon’s Format tab, in the Current Selection group, click Format Selection.
  3. In the Axis Options category, add a check mark to Categories in Reverse Order.
    • LateNight07
  4. The horizontal axis moves to the top, and you’ll format it to stay at the bottom. In the Horizontal Axis Crosses section, select At Maximum Category.


Format the Horizontal Axis

Next, you can format the horizontal axis, so it shows a set range of times and intervals. With the Format Axis dialog box still open, follow these steps.

  1. Click on one of the times in the chart, to select the horizontal axis.
  2. In the Axis Options category, for Minimum, select Fixed, and change the decimal places to .875 which is 9:30 PM.
  3. For the Major Unit, select Fixed, and set the units to 0.0625 which is 1.5 hours.
  4. Close the Format Axis dialog box.


Format the Gantt Chart

To finish the Gantt chart, you can format it. For example, you can remove the legend, add a Title, and change the fill colour for the duration series. Here are my completed Gantt charts. I created and formatted the first chart, then copied its formatting to the second chart, by using Paste Special, Formats.

Compare the Charts

With the old and new schedules in Gantt charts, it’s easy to see how the late night viewing times will shift to later hours. I usually watch Letterman, so the schedule change won’t affect me, but some people might be a bit more tired in the morning.
To play with the New Schedule, you can change the start time for the first show, or change any of the show durations. The Gantt chart will adjust to show your revisions.

More on Gantt Charts

To learn more about Excel Gantt charts, visit Jon Peltier’s website, and check his list of Excel Gantt Chart resources. There are links to a couple of Jon’s excellent Gantt chart articles, and many other helpful links.