Creating the schedule spreadsheet:
Tools
Add-Ins
Analysis ToolPack
Analysis Toolpack VBA
OK
A / B / C / D / E / F1 / Task # / Task Name / Start Date / End Date / Time elapsed
(Cx - $C$start) / Duration
Dx - Cx+1
2 / 1 / Task 1 / =C3 / =D6 / =C2-$C$3 / =D2-C2+1
3 / 1.1 / Activity 1 / 11-May-02 / =C3+4 / =C3-$C$3 / =D3-C3+1
4 / 1.2 / Activity 2 / =D3+3 / =C4+4 / =C4-$C$3 / =D4-C4+1
5 / 1.3 / Activity 3 / =D4+3 / =C5+4 / =C5-$C$3 / =D5-C5+1
6 / 1.4 / Activity 4 / =D5+3 / =C6+4 / =C6-$C$3 / =D6-C6+1
7 / 2 / Task 2 / =C8 / =D11 / =C7-$C$8 / =D7-C7
8 / 2.1 / Activity 1 / =EDATE(D3,1) / =C8+11 / =C8-$C$8 / =D8-C8
9 / 2.2 / Activity 2 / =D8-4 / =C9+18 / =C9-$C$8 / =D9-C9
10 / 2.3 / Activity 3 / =EDATE(C8,1) / =C10+2 / =C10-$C$8 / =D10-C10
11 / 2.4 / Activity 4 / =C9+7 / =C11+18 / =C11-$C$8 / =D11-C11
12 / 3 / Task 3 / =C13 / =D16 / =C12-$C$8 / =D12-C12
13 / 3.1 / Activity 1 / =EDATE(C8,1) / =C13+9 / =C13-$C$8 / =D13-C13
14 / 3.2 / Activity 2 / =D13+3 / =C14+18 / =C14-$C$8 / =D14-C14
15 / 3.3 / Activity 3 / =D14-11 / =C15+18 / =C15-$C$8 / =D15-C15
16 / 3.4 / Activity 4 / =D15-11 / =C16+4 / =C16-$C$8 / =D16-C16
For each task, you should enter/calculate a start date and an end date. If you calculate your dates based on other tasks, then the impact of an adjustment of one date on the overall timeline will be shown automatically. This interdependency between tasks takes careful planning, documentation, and checking but once completed, project updates are quick and easy.
The time elapsed column tells your bars on your Gantt chart where to start – how many days after the start of the project. The duration column tells the bars how long they should be. The shaded columns are the columns that you will select to create your Gantt chart.
Creating the Gantt chart:
Select the three columns: Task Name, Time elapsed, and Duration. To select individual columns or rows, click the gray header (letters for columns, numbers for rows), then hold down the Ctrl key while you choose the other columns/rows you would like to include in your selection. To select a range of columns or rows, hold the Shift key down and select the beginning and the end of your range.
Go to Insert – Chart
Select the Custom Types tab in the pop-up dialog box: Chart Wizard
Step 1: Next select Floating Bars from the list of custom chart types. Then hit the Next button.
Step 2:The Chart Wizard will ask you to check the columns/rows you want included in the chart by highlighting your selection/s with a blinking line. If you need to make a change to your selection, hit the red/white button to the right of the Data range: box. Then select your data in the Excel worksheet and hit the little button to the right of the floating bar with a red arrow on it. This enters the updated selection range in the dialog box.
Make sure that you mark that your Series are in Columns, NOT Rows. Then hit Next.
Step 3: This next dialog box is where you enter the text for your Chart title, Category (X) axis, and Value (Z) axis. Once you have finished entering your label text, hit Next.
Step 4: In this dialog box you decide where you’d like to place your chart. You can either place it as a picture/object on the currently active worksheet, which contains the data, or you can choose to have your chart on a new sheet. I usually select to place my charts on New Sheets because I’ve found it causes fewer problems. The click Finish.
Now you are on your chart page. The first thing you must do is place your cursor over the x axis labels and right-click the mouse button.
A small box will pop-up which will say Format Axis and Clear. Choose Format Axis.
When the dialog window appears, go to the Scale tab and check the last box, which states: Categories in reverse order and click the OK button.
Grouping Tasks and Activities:
An interesting feature of Excel is that it will only graph what is visible in the data source worksheet. This means that you can graph summary information of detailed operations for an overview. You can also make several different graphs – one for each task group. In order to take advantage of these features, we need to group and outline our data.
In your worksheet, select rows 3 – 6, or all the activities in Task 1.
Go to Data, Group and Outline, and click Group.
You will see a black bar appear to the left of the row numbers 3- 6, and a box with a minus sign on it at the bottom. Click on the minus sign button, and you’ll see that the activities you just selected are “hidden” under Task 1. Finish Grouping the other activities.
After you are done Grouping, notice that there are two numbers at the top of the new gray space to the left of the row numbers. These refer to the number of outline levels in your data. If you click on the number 1, you’ll see only the first level of data. If you click on the number 2, you’ll see the second level of data. The higher the number, the more detail you will see.
There is no limit to the number of groupings you can do. Let’s add a header to our three Tasks called Project. Insert a row before Task 1 and call the row Project.
Now select rows 3-17 and Group. Now your outline has 3 levels: Project, Task and Activities. When grouping data with multiple levels you must always group together all the lowest levels first, then all the second lowest levels (including the lowest levels) and so on.
Select the 2nd outline level and see how it changes the chart we made.
Date functions:
WORKDAY
Returns a number that represents a date that is the indicated number of working days before or after start_date. Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed. To view the number as a date, click Cells on the Format menu, click Date in the Category box, and then click a date format in the Type box.
WORKDAY(start_date,days,holidays)
Start_dateis a date that represents the start date.
Daysis the number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.
Holidaysis an optional list 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 or an array constant of the numbers that represent the dates.
Remarks:
If start_date is not a valid date, WORKDAY returns the #NUM! error value.
If start_date plus days yields an invalid date, WORKDAY returns the #NUM! error value.
If days is not an integer, it is truncated.
Examples:
WORKDAY(DATEVALUE("01/03/91"), 5) equals 33248 or 01/10/91
If January 7, 1991 and January 8, 1991 are holidays, then:
WORKDAY(DATEVALUE("01/03/91"), 5, {33245, 33246}) equals 33252 or 01/14/91
EDATE:
Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.
EDATE(start_date,months)
Start_dateis a date that represents the start date.
Monthsis the number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.
Remarks:
If start_date is not a valid date, EDATE returns the #NUM! error value.
If months is not an integer, it is truncated.
Examples:
EDATE(DATEVALUE("01/15/91"),1) equals 33284 or 02/15/91
EDATE(DATEVALUE("03/31/91"),-1) equals 33297 or 02/28/91
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.
NETWORKDAYS(start_date,end_date,holidays)
Start_dateis a date that represents the start date.
End_dateis a date that represents the end date.
Holidaysis an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays.
Remark:
If any argument is not a valid date, NETWORKDAYS returns the #NUM! error value.
Example:
NETWORKDAYS(DATEVALUE("10/01/91"), DATEVALUE("12/01/91"),
DATEVALUE("11/28/91")) equals 43
DATEDIF
Returns the difference between the start_date and end_date, in the specified unit of time, rounded to whole numbers.
DATEDIF(start_date,end_date,”time unit”)
Start_dateis a date that represents the start date.
End_dateis a date that represents the end date.
“Time unit”an abbreviation, in parenthesis, for a unit of time. “d” = days, “m” = months, “y” = years
Example:
DATEDIF(C2,D2,"m")
How Microsoft Excel performs date and time calculations
Microsoft Excel stores dates as sequential numbers known as serial values and stores times as decimal fractions because time is considered a portion of a day. Dates and times are values and therefore can be added, subtracted, and included in other calculations. For example, to determine the difference between two dates, you can subtract one date from the other. You can view a date or time as a serial number or a decimal fraction by changing the format of the cell that contains the date or time to General format.
Microsoft Excel 97 supports two date systems: the 1900 and 1904 date systems. The default date system for Microsoft Excel 97 for Windows is 1900. To change to the 1904 date system, click Options on the Tools menu, click the Calculation tab, and then select the 1904 date system check box.
The following table shows the first date and the last date for each date system and the serial value associated with each date.
Date
SystemFirst DateLast Date
1900January 1, 1900 December 31, 9999
(serial value 1)(serial value 2958525)
1904January 2, 1904 December 31, 9999
(serial value 1)(serial value 2957063)
Note When you enter a date in Microsoft Excel 97 and you enter only two digits for the year, Microsoft Excel enters the year as follows:
- The years 2000 through 2029 if you type 00 through 29 for the year. For example, if you type 5/28/19, Microsoft Excel assumes the date is May 28, 2019.
- The years 1930 through 1999 if you type 30 through 99 for the year. For example, if you type 5/28/91, Microsoft Excel assumes the date is May 28, 1991.
Tips on entering dates and times
Microsoft Excel treats dates and times as numbers. The way that a time or date is displayed on a worksheet depends on the number format applied to the cell. When you type a date or time that Microsoft Excel recognizes, the cell's format changes from the General number format to a built-in date or time format. By default, dates and times are right-aligned in a cell. If Microsoft Excel cannot recognize the date or time format, the date or time is entered as text, which is left-aligned in the cell.
- Options you select in the Regional Settings of Control Panel determine the default format for the current date and time and the characters recognized as date and time separators¾ for example, the colon (:) and slash (/) on United States-based systems.
- To type a date and time in the same cell, separate the date and time with a space.
- To type a time based on the 12-hour clock, type a space followed by AM or PM (or A or P) after the time. Otherwise, Microsoft Excel bases the time on the 24-hour clock. For example, if you type 3:00 instead of 3:00 PM, the time is stored as 3:00 AM.
Regardless of the format used to display a date or time, Microsoft Excel stores all dates as serial numbers and stores all times as decimal fractions. To display a date as a serial number or display a time as a fraction, select the cells that contain the date or time. On the Format menu, click Cells, click the Number tab, and then click General in the Category box.
Times and dates can be added, subtracted, and included in other calculations. To use a date or time in a formula, enter the date or time as text enclosed in quotation marks. For example, the following formula would display a difference of 68:
="5/12/94"-"3/5/94"
In Microsoft Excel for Windows (and Lotus 1-2-3), days are numbered from the beginning of the century; the date serial number 1 corresponds to the date January 1, 1900. Microsoft Excel for the Macintosh uses the 1904 date system; the date serial number 1 corresponds to January 2, 1904. To change the date system for use in calculations, click Options on the Tools menu, and then click the Calculation tab. Under Workbook options, select the 1904 date system check box. Use the 1904 date system for a workbook if you use that workbook with other workbooks that use the 1904 date system.
If you open in Microsoft Excel for Windows a file created in Microsoft Excel version 2.0 or later for the Macintosh, Microsoft Excel recognizes the file format and automatically changes dates to the 1900 date system. Similarly, if you open a Microsoft Excel for Windows file on a Macintosh, Microsoft Excel changes dates to the 1904 date system.
WEEKDAY
Returns the day of the week corresponding to serial_number. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday).
WEEKDAY(serial_number,return_type)
Serial_numberis the date-time code used by Microsoft Excel for date and time calculations. You can give serial_number as text, such as "15-Apr-1993" or "4-15-93", instead of as a number. The text is automatically converted to a serial number. For more information about serial_number, see NOW.
Return_typeis a number that determines the type of return value.
Return_typeNumber returned
1 or omittedNumbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
2Numbers 1 (Monday) through 7 (Sunday).
3Numbers 0 (Monday) through 6 (Sunday).
Remarks:
- Microsoft Excel for Windows and Microsoft Excel for the Macintosh use different date systems as their default. For more information, see NOW.
- You can also use the TEXT function to convert a value to a specified number format when using the 1900 date system:
TEXT("4/16/90", "dddd") equals Monday
Examples:
WEEKDAY("2/14/90") equals 4 (Wednesday)
If you are using the 1900 date system (the default in Microsoft Excel for Windows), then:
WEEKDAY(29747.007) equals 4 (Wednesday)
If you are using the 1904 date system (the default in Microsoft Excel for the Macintosh), then:
WEEKDAY(29747.007) equals 3 (Tuesday)
DATE: Returns the serial number of a particular date. For more information about serial numbers, see NOW.
DATE(year,month,day)
Yearis a number from 1900 to 9999 in Microsoft Excel for Windows or 1904 to 9999 in Microsoft Excel for the Macintosh.
Monthis a number representing the month of the year. If month is greater than 12, then month adds that number of months to the first month in the year specified. For example, DATE(90,14,2) returns the serial number representing February 2, 1991.
Dayis a number representing the day of the month. If day is greater than the number of days in the month specified, then day adds that number of days to the first day in the month. For example, DATE(91,1,35) returns the serial number representing February 4, 1991.
Remarks:
- Microsoft Excel for Windows and Microsoft Excel for the Macintosh use different date systems as their default. For more information, see NOW.
- The DATE function is most useful in formulas where year, month, and day are formulas, not constants.
Examples:
Using the 1900 date system (the default in Microsoft Excel for Windows), DATE(91, 1, 1) equals 33239, the serial number corresponding to January 1, 1991.
Using the 1904 date system (the default in Microsoft Excel for the Macintosh), DATE(91, 1, 1) equals 31777, the serial number corresponding to January 1, 1991.
How Microsoft Excel converts values in formulas
When you enter a formula, Microsoft Excel expects certain types of values for each operator. If you enter a different type of value than is expected, Microsoft Excel sometimes is able to convert the value.
The formula / Produces / Explanation="1"+"2" / 3 / When you use a plus sign (+), Microsoft Excel expects numbers in the formula. Even though the quotation marks mean that "1" and "2" are text values, Microsoft Excel automatically converts the text values to numbers.
=1+"$4.00" / 5 / When a formula expects a number, Microsoft Excel converts text if it is in a format that would usually be accepted for a number.
="6/1/92"-"5/1/92" / 31 / Microsoft Excel interprets the text as a date in the mm/dd/yy format, converts the dates to serial numbers, and then calculates the difference between them.
=SQRT("8+1") / #VALUE! / Microsoft Excel cannot convert the text to a number because the text "8+1" cannot be converted to a number. If you use "9" or "8"+"1" instead of "8+1", the formula will convert the text to a number and return the result of 3.
="A"&TRUE / ATRUE / When text is expected, Microsoft Excel converts numbers and logical values such as TRUE and FALSE to text.