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.

LateNight01

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.

LateNight05

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.

LateNight04

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.

LateNight06

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.

______

You may also like...

12 Responses

  1. Jon Peltier says:

    The 90-minute major tick division is a bit unusual. And the actual shows mentioned are becoming less relevant. Seinfeld and Simpsons reruns are shown here at 11:30 to 12:30.

  2. Thanks Jon, and to show 1 hour tick divisions we could use 0.0416666666666667 as the major unit.

  3. Jon Peltier says:

    Or if you were using Excel 2003, you could just enter a time, like 1:00. That’s broken in 2007, and I haven’t tried in 2010.

  4. JP says:

    The Gantt charts look like a ratings chart — falling ever so quickly downward.

    And isn’t the new (proposed) schedule putting Leno at 11:30, not 12?

  5. JP, you’re right, it does highlight the downward trend! And the screen shots were done after I had changed the start time for the New Schedule, so the news starts at 11:30. That way, you can watch Letterman and Conan, just like the old days. ;-)

  6. Jean Paul Roche says:

    Thanks Debra for this course,I entered only the hours and the chart work correctly with reformating the horizontal axis without the date : mini=.875, maxi=1.125, major unit=0.041666667 as stated by Jon Peltier.
    Do you know if it’s possible to enter formula in the format axis window options eg : = 1/24 for 1 hour ?
    Please note that the decimal place .875 is 9:00PM instead of 9:30PM.

  7. Jon Peltier says:

    Jean Paul –

    You can only enter values in these boxes, and unfortunately you cannot link them to cells in the worksheet.

  8. Arian says:

    A B C

    1 1-5
    12 6-20
    1 21-50
    35 51-100
    87 101-200
    910 201-300
    500 301-400
    300 401-500
    400 501-600
    894 601-700
    14 701-800
    1 801-900
    1 901-1000
    1 1001-1250
    1 1251-1500
    3 1501-1750

    I have a data colum A and trying to put a banding which is in another colum C
    How do I display beside each number the banding?
    is there any function which deos this?

    I appreciate your help

    Arian

  9. Arian, you can insert a blank column, to the left of column C, and enter the minimum value for each range.
    Then, in column B, use a Vlookup formula to find the closest value.
    There’s an example here: Create an Excel VLookup formula for a range of values

  10. […] And if you’re trying to make a timeline in Excel there are instructions here for an Excel Gantt Chart. […]

  11. Chester Tugwell says:

    This is a great solution if you don’t need the heavy weight features of MS Project. If you really want to get into using Excel as a project management tool there are some advanced templates available such as vertex42.com

  12. XLCalibre says:

    Here’s an alternative method, using conditional formatting instead of a traditional graph: http://xlcalibre.com/hr-dashboard-gantt-chart-traffic-light-report
    Its been set up for longer periods of time, so won’t help with your TV schedule, but it makes it easier to view things in a days, then weeks etc, without bringing up a dialogue box and thinking what decimal to put it. What do you think?
    http://xlcalibre.com/hr-dashboard-gantt-chart-traffic-light-report

Leave a Reply to Jon Peltier Cancel reply

Your email address will not be published. Required fields are marked *