Claris Impact: Creating a Timeline

Picture this: Your boss just left your office insisting that a timeline for the Sushi Bar Project plan had to be completed by the staff meeting on Friday. Unfortunately you don't have ClarisImpact, a software package that does timelines, nor do you have time to get it, let alone learn how to use it. Fortunately, you do have ClarisWorks and thought of creating a drawing with the timeline information in it. This would work if the dates were firm and you had to do it once, but, as with all projects, the dates are cast in Jell-O and a drawing would be very tedious to change every time a date changed. Then you realize that, with a little effort, you could simulate a timeline by creating a spreadsheet that uses the Repeat function.

Timelines are graphic representations that show all of the separate tasks required to complete a project in the sequence in which they occur. Consequently, you must know the tasks required and the starting date and projected duration of each task. Each task would occupy a row in the spreadsheet and normally, but not always, are listed in starting date order. Occasionally it is desired to have related tasks on adjacent rows to emphasize the relationship between them.

To create your timeline, open a new ClarisWorks spreadsheet and create a title for the timeline by typing "Sushi Bar Project" in cell F1. Choose an appropriate font, size and center justify the text from the Format menu. In the example shown in Figure 1, the title has been set to Palatino, 18 point, bold, center-justified. Also, the height of Row 1 has been adjusted to 24 to fit the larger font size of the title.

Go to cell C3 and type "Start Date", press enter. Move to Cell C4 and select Number... from the Format menu. Choose the date format you prefer from the date format options under this menu. C4 is the cell that will be used to enter the actual start date of the project and will be used by calculations entered into other cells. Type in the actual start date of your project into cell C4. (Figure 1 uses 4/3/95 as a start date.)

In cell C5, type the following function:

=DAYNAME(WEEKDAY(C4))

This will place the name of the day of the week in that cell. Select the cells in B, C and D by clicking, and dragging through as many rows as you have tasks. Next, choose

Center from the Alignment sub-menu in the Format menu to format the cells.

Type the text, "Now:" into cell D5. Format with Right Alignment and a Bold font style. This is a label for the cells to the right of it that are going to indicate where the current date falls in the project.

Cell E5 is the first example of the use of the Repeat Function in our timeline. The repeat function has two arguments, the text that is to be repeated and a number (or calculation) that determines how many times the text is to be repeated. If you choose Paste FunctionÉ from the Edit menu, the Repeat function would appear as: REPT(Text , #_of_times).
If you typed "=REPT("xxxooo",4)" in a cell the result of the function would be: xxxoooxxxoooxxxoooxxxooo.

Go to Cell E5 and type the following:

=REPT("-", NOW()-C4) & "|"

The first argument ("-") is the text, a hyphen enclosed in quotes, that repeats in this field. The second argument, NOW()-C4, is equal to the number of days between the current date, "NOW", and the starting date of the project which is entered in Cell C4 and is the number of hyphens that will be entered in this cell. The & "|" at the end of the calculation will show the location of the current date.

Cell E5, and the other cells that use the repeat function, must be formatted using a mono-spaced font such as Courier. You are using a text character to display a graphic representation of time. Since time is made up of a number of increments of constant width (days in this case), the unit selected to represent time must also be a constant dimension. To make things easy to format, select the cell range from E5 to M12 and format these cells as follows: font of Courier, font size of 12 Points, Column Width of 48. The actual area used will vary according to the number of days in your project. It is essential that the graph (or timeline) area be formatted in this manner.

The next step is to add the dates to the graph area. First of all, select cells E6 through M6 and specify a date format from the Number... sub-menu of the Format menu. Go to Cell E6 and enter:

=C4

This sets the first column of the graph to the start date of the project. Tab (or move) to Cell F6 and enter the formula:

=E6+7

This adds one week to the start date. Select cells F6 through M6 and then choose Fill Right from the Calculate menu. This gives you nine weeks of dates for your project. It may be necessary to reformat the text in Row 6 to a 9 point font size so the dates will fit in the columns. If your project is longer than nine weeks, add a column for each additional week. (Remember to format the additional columns as Courier with a columns width of 48, and to format Row 6 to display a date format.)

Type the text, Task, From, To, and Duration as headings in Row 7 of Columns A, B, C, and D, respectively (refer to Figure 1 for guidance, if necessary). In cell E7 type the second use of the repeat function:

=REPT("^", WEEKDAY($C$4)-1) & "|"

[For clarity, the "^" indicates where a space should be typed.]
This places a vertical bar reference mark within the cell that approximates the position of the day of the week for the date in the cell above it. Select Cells E7 through M7 and choose Fill Right from the Calculate menu. This puts the reference mark below each date in the timeline.

Starting in Row 8, enter the tasks required for the project in Column A. Enter the From dates for each task in Column B. Enter the End dates for each task in Column C. Then go to Cell D8 and enter the formula:

=C8-B8

Starting at Cell D8, select it and the cells below it for as many tasks as you have in your project and choose Fill Down from the Calculate menu. This formula calculates the number of days from the beginning to the end of each task. In the example in Figure 1, you would select cells D8 down to D12 before choosing the Fill Down command.

To create the actual timeline bar you use the Repeat function twice in the same formula. The first Repeat function will put spaces in the bar for the number of days between the start date of the project and the start of the task. The second Repeat function will place bullets (Option-8 on the Macintosh keyboard; on Windows hold down the Alt key and type 0149 on the numeric keypad) in the bar from the start of the task to the end of the task. You can use any mono-spaced character in place of the bullets, such as diamonds (Option-Shift-V on the Macintosh; on Windows hold down the Alt key and type 0224 on the numeric keypad) or triangles (Option-J on a Macintosh).

To create your timeline bar formula, go to Cell E8 and enter:

=REPT("^", B8-$C$4+1) & REPT("¥", D8)

[For clarity, the "^" indicates where a space should be typed.]
Starting at Cell E8, select the cells below it for as many tasks as you have in your project and choose Fill Down from the Calculate menu (in the example, cells E8 through E12 were selected). Again, the cells that contain the Repeat function must be formatted with a mono-spaced font in order to display the bar accurately.

This is a quick, simple timeline that may fill your needs when you are in a jam. If you wish to have a full featured timeline capability, have your dealer give you a demonstration of ClarisImpact.