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
Late Night TV Show Times
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.
- Select a cell in the current schedule table.
- On the Ribbon’s Insert tab, click Bar, then click Stacked Bar
- 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.
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.
- Click on one of the show names in the chart, to select the vertical axis.
- On the Ribbon’s Format tab, in the Current Selection group, click Format Selection.
- In the Axis Options category, add a check mark to Categories in Reverse Order.
- 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.
- Click on one of the times in the chart, to select the horizontal axis.
- In the Axis Options category, for Minimum, select Fixed, and change the decimal places to .875 which is 9:30 PM.
- For the Major Unit, select Fixed, and set the units to 0.0625 which is 1.5 hours.
- 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.
Gantt Chart Tutorial & Video
Go to the Gantt Chart tutorial, for step-by-step videos and written steps. and a sample file to download.
More Excel Charts
To learn more about Excel chart, go to the following pages on my Contextures site.
______
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.
Thanks Jon, and to show 1 hour tick divisions we could use 0.0416666666666667 as the major unit.
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.
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?
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. 😉
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.
Jean Paul –
You can only enter values in these boxes, and unfortunately you cannot link them to cells in the worksheet.
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
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
[…] And if you’re trying to make a timeline in Excel there are instructions here for an Excel Gantt Chart. […]
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