Introduction

The paper begins with an easy to follow demonstration taken from an excellent book on the subject of Excel spreadsheets. Then we are going to work on an example of a table of data that relates to the holiday rota of people in an organisation. The list contains the start and end dates of any holidays that these members of staff have taken, are taking, or are going to take in the current year; and we will be using a Gantt chart to transform the names and dates into useful information.

Having gained confidence in using Gantt charts, the final part of the paper takes us through a multiple activity Gantt chart. The chart used looks a bit complex but we should appreciate that it contains many data points; but once it's in chart form it becomes really simple to read and use. This part of the page also includes detailed instructions on how to use the Excel Function NETWORKDAYS: a very useful function that can save both time and the capacity for errors! The NETWORKDAYS function is a versatile function that can be used in a variety of situations: read Excel's Help files for further guidance here.

Creating Simple Gantt Charts

Take a look at Table 1: imagine that we want to visualise how the project to which that table relates is progressing: the order in which they are progressing, the length of time each activity takes and so on. Difficult, isn't it? A table of data in such cases is of limited value, so how can we present such data so that it can answer the questions we need?

One way is to buy or use Project Management Software: such software will take a table such as Table 1 and prepare automatically a GANTT chart for us. Alternatively, our solution is to program an Excel Spreadsheet to prepare a Gantt Chart for us. More than that, we are going to prepare the chart in such a way that we obtain maximum benefit from it given that the average printer will print out onto a piece of A4 paper.

The section that follows is based on John Walkenbach's Microsoft Excel 2000 Bible published by IDG Books in 1999

"Simple Gantt Charts

Creating a simple Gantt chart isn't difficult when using Excel, but it does require some setup work. Gantt charts are used to represent the time required to perform each task in a project. Table 1, shows data that was used to create a Gantt chart.

It is best if you read this part of the paper and have Excel open and running on your computer at the same time. You can then practice exactly what we are describing here to make sure everything works and you understand it.

Table 1 Data used in the Gantt chart

task	start date	duration	end date
planning meeting	29/12/97	1	29/12/97
develop questionnaire	30/12/97	11	09/01/98
print and mail questionnaire	13/01/98	9	21/01/98
receive responses	16/01/98	15	30/01/98
data entry	16/01/98	18	02/02/98
data analysis	03/02/98	4	06/02/98
write report	09/02/98	12	20/02/98
distribute draft report	23/02/98	1	23/02/98
solicit comments	24/02/98	4	27/02/98
finalise report	02/03/98	5	06/03/98
distribute to board	09/03/98	1	09/03/98
board meeting	17/03/98	1	17/03/98

Here are the steps to create this chart in MS Excel: Figure 1 is the chart you should end up with.

1 Enter the data as shown in Table 1. The formula in cell D2 (end date), which was copied to the rows below it, is =B2+C2-1.

2 Use the Chart Wizard to create a stacked bar chart from the range A2:C13. Use the second subtype, which is labeled Stacked Bar.

Figure 1 A Gantt Chart Created From a Bar Chart

3 In Step 2 of the Chart Wizard, select the Columns option. Also, notice that Excel incorrectly uses the first two columns as the Category axis labels.

4 In Step 2 of the Chart Wizard, click the Series tab and add a new data series. Then, set the chart's series to the following:

	Series 1: B2:B13
	Series 2: C2:C13
	Category (x) axis labels: A2:A13

5 In Step 3 of the Chart Wizard, remove the legend and then click Finish to create an embedded chart.

6 Adjust the height of the chart so that all the axis labels are visible. You can also accomplish this by using a smaller font size.

7 Access the Format Axis dialog box for the horizontal axis. Adjust the horizontal axis Minimum and Maximum scale values to correspond to the earliest and latest dates in the data (note that you can enter a date into the Minimum or Maximum edit box). You might also want to change the date format for the axis labels.

8 Access the Format Axis dialog box for the vertical axis. In the Scale tab, select the option labeled Categories in reverse order, and also set the option labeled Value (y) axis crosses at maximum category.

9 Select the first data series and access the Format Data Series dialog box. In the Patterns tab, set Border to None and Area to None. This makes the first data series invisible.

10 Apply other formatting, as desired."

If you are not fully confident, feel free to work through Walkenbach's example several times.

What follows is new material from me

Holiday Schedule and Gantt Chart

As we said above, Table 1 is part of the list of 80 employees working in an organisation, their holiday details, their department name and position in their department.

Table 2 Basic Holiday Schedule Data

This table is big, click here to see it in a separate window.

So, let's imagine that we had the full table of 80 staff members in front of us now and then imagine that we need to determine who is at work at the moment, who is on holiday, when will Mr or Mrs X be returning from holiday and so on. Figure 2 is the Gantt Chart of the full holiday schedule

Figure 2 Holiday Gantt Chart

A full size version of figure 2 is also available in the separate window.

How useful is this Gantt Chart? Not very! Basically, this chart includes everyone who has declared their holiday plans. We can see that there is a bar for everyone that shows the duration of their holiday and when it takes place.

The problem with Figure 2, though, is that there are too many people on it so it is difficult to read, especially the names. Since it's difficult to read, it's difficult to take any useful information from it.

Solution: Use Monthly Charts

Consequently, we should break down the information we have and prepare sub graphs from our data. One way of doing this, with the holiday schedule is to create separate graphs for June, July, August, September …

Firstly, the table of data. Sort the data into date order: start date. It might also help to colour code the data in the table as a way of helping to identify the data from month to month. Table 3 shows an extract of the data sorted by starting date and then colour coded: in case you don't have a colour printer, the months are separated by horizontal lines as well … see how helpful we are! We've deleted the Department and Position columns too as we don't need them.

Table 3 Annual Leave Schedule

This table is also best looked at in a separate page, click here to see it in a separate page: it is in the same page as Table 2 so if you have that page open already, there is no need to click here.

All we need to do now is to COPY the chart we already have in Figure 2 and then delete the data we don't need. Let's start with the example of June …

Select, copy and paste the chart in the excel file. Move it to where you would like it to be: somewhere on the sheet the data are on or onto a separate sheet.

Now select the whole chart and right click on it. You should get a dialogue box that includes the words Source Data … If not, maybe you didn't click in the right place: when you have the mouse over the chart, a little box should appear that says Chart Area, then you can right click. The Source Data dialogue box looks like Figure 3:

Figure 3 The Source Data Dialogue Box

Series by Series, you need to do the following:

For the Start Date series

Click on Source Data and you are faced with another dialogue box that shows Data Range and Series tabs: click on the Series tab. Left click the little icon at the right hand side of the Values field and select the Start Date data for June and only June. Do the same for Category (X) axis labels and choose the names of people taking a holiday in June and only June.

Repeat the above for the Days series and the End Date series, although in these two cases you do NOT need to change the Category (X) axis labels since that is taken care of automatically. You will probably have to reformat the horizontal, X, series of the graph itself so that it goes from 1 to 30 June only; but apart from that, you've done it! Figure 4 shows you what you might have got.

The advantage of this new, monthly, chart is that we only have five people on holiday in June so the June Chart is nice and compact and REALLY easy to read.

Figure 4 June's Holiday Chart

Do the same for the data you have in Table 3 relating to July. If you want all of the information for this exercise, just e-mail me and I'll happily send the full table of data by return … format what you like as you like until you're happy!

NETWORKDAYS Function

Excel's help file concerning the NETWORKDAYS function reads as follows

"NETWORKDAYS Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.

Syntax

NETWORKDAYS(start_date,end_date,holidays)

Start_date is a date that represents the start date. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

End_date is a date that represents the end date.

Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates. Learn about array constants. For more information about how Microsoft Excel uses serial numbers for dates, see the Remarks section.

Remark

• Excel stores dates as sequential serial numbers so that it can perform calculations on them. Excel stores January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900. Learn more about how Microsoft Excel stores dates and times.
• If any argument is not a valid date, NETWORKDAYS returns the #NUM! error value.

Examples

Given a project that begins October 1, 1998, and ends December 1, 1998, the following example calculates the number of days in the project. November 26, 1998, is a holiday and is excluded in the calculation.

NETWORKDAYS("10/01/1998","12/01/1998","11/26/1998") equals 43.

The following example returns the number of workdays for a project that begins on October 1, 1999, and ends on February 15, 2000; it excludes December 24, 1999, and January 3, 2000. NETWORKDAYS("10/01/1999",

"2/15/2000",{"12/24/1999","1/3/2000"}) equals 96

The following example calculates the number of workdays between May 1, 2002 and May 31, 2002, inclusive; it excludes May 28, 2002.

NETWORKDAYS("2002/05/01","2002/05/31","2002/05/28") equals 22"

3 September 2001 revised 20 May 2002