This file is available in our store. Contents:
Purpose
A Gantt chart is a common tool for managing project tasks and milestones. If you’re familiar with Gantt charts you might ask, “why another Gantt?” Certainly if you use MS Project or similar tool you don’t need to build one in Excel. If your need is occasional and simple, there are plenty of nifty Gantt templates to choose from. This project incorporates the Calendar sheet from the Startup File project, and introduces some uncommon features that serve to illustrate some advanced date and format techniques.
Capacity: set custom work week, holidays and other down days
Zoom: four column bucket options, from day to month
Stages: up to seven sequential stages in different colors
Events: up to seven discrete instances per repeating task
Failure to plan capacity is the downfall of many projects. Of course, many wise managers will build in additional time to account for peak holidays and probable vacation periods. For a day or two here and there it is close enough, but having a clear list of non-working dates adds transparency. Custom down days and weekly patterns support unique project scenarios. Duration is set in either workdays or calendar days per task.
Zoom enables the manager to adjust to the audience, with daily bucket detail for weekly team meetings, and weekly or monthly bucket overview for management review.
Stages allow for sequential related tasks on a single row, in up to seven different colors. An example is a change release, with multiple changes going through standard stages of design, build, test, pilot, rollout and validation. Listing each stage for each change as separate rows would take a lot of space, while not adding value.
Discrete events such as monthly reviews can be entered like stages, but the length is the separation between events rather than their duration. For example, Nth weekday of the month can be separated by either 28 or 35 calendar days. Discrete events all have the same color which is different from the stage colors.
Structure
The structure of a Gantt is a transposed calendar with a horizontal time scale. The vertical dimension comprises the tasks of the project, which appear in the timescale columns as bars, the width of duration. They are listed in more or less chronological order, so that the bars appear like a somewhat uneven staircase going diagonally down from top left to bottom right.
A common challenge of project management is the scale of the time dimension. The granularity of daily columns support detailed task management, but often need to be manually converted to weeks or months to provide the full horizon view to management. This Gantt makes that conversion with the click of an option button to select month, week or day periods.
The task list is formatted as a table named Project, convenient for insertion, deletion and extension of rows, and for referencing. There are three calculated columns, and many more named formulas that reference the table. The graphic side of the table has no contents, it is only formatting, for which the calculation is performed via names.
Period Headers
The time column headers are six rows not including the table header. The period is represented in five units; Year, Quarter, Month, Week and Day. The sixth row serves two purposes. With Day columns, it gives the weekday number, counting from 1=Monday. With Week or Month columns, it gives the number of workdays in the period.
The Day row of the header calculates the date from which the other rows are calculated. The period size selected is represented by an index number 1 to 4; 1 =calendar days, 2 =workdays, 3 =weeks, 4 =months. PerAll is a checkbox to “show all days”, and PerOpt is the linked cell of the 3-button option group: Month (1), Week (2), Day (3).
PerIndex =IF(AND(PerAll,PerOpt=3),1,5-PerOpt)
The date is one formula for the first period, and another for all the other periods. The first column indexes to the first day of the period in which the project starts.
PerDate =IF(ISTEXT(Left),PerFirst,PerRest)
PerFirst =CHOOSE(PerIndex,ProjectStart,ProjectStart,ProjectStart-WEEKDAY(ProjectStart,WdType)+1, DATE(YEAR(ProjectStart),MONTH(ProjectStart),1))
The other columns use a relative name Left =the cell to the left, similar to how I have used Above in vertical tables. Uncon is another checkbox to “use all days”, to see the unconstrained schedule. WkString and Days[OffDays] are from Calendar.
PerRest =CHOOSE(PerIndex, Left+1,WORKDAY.INTL(Left,1, IF(Uncon,"0000000",Wkstring), IF(Uncon,0,Days[OffDays])),Left+7,EDATE(Left,1))
The Workdays formula returns weekday for Day bucket (PerIndex 1 or 2), and workdays for Week or Month bucket. This uses NETWORKDAYS.INTL from PerThis (this column’s date) to PerNext (next column’s date). The IF(PerNext statement accounts for the last column not having any PerNext value by making an equal jump from PerPrev (previous column’s date).
Workdays =IF(PerIndex<3,WEEKDAY(PerThis,WdType), NETWORKDAYS.INTL(PerThis,IF(PerNext,PerNext,PerThis+(PerThis-PerPrev))-1,IF(Uncon,"0000000",Wkstring), IF(Uncon,0,Days[OffDays])))
Configuration
Configuration of holidays, other down days, week pattern and date format is done in the Calendar Sheet, which is copied from the Startup project. Dates with a “count” checkbox apply here as different ways to maintain project downtime. That includes date ranges, date list, and calculated or entered holidays.
Calendar highlights each date type differently and can be used to document the down periods, while the project schedule simply considers them all equally down, if their “count” checkbox is set, corresponding to Calendar’s Buckets table count of workdays.
Date format is the combination of date order and date separator settings. Work week selection is one of seven 5-day weeks, or a custom string of seven binary digits to define working and non-working days. These are described in more detail in Chapter 4 Startup.
The Plan sheet has two areas of configuration, the first is the stage legend. This is simply a range of small cells numbered S1 to S7, plus D for discrete, where each cell is the color of the stage on the chart. Stages are optional and work best for well-known stages that can be described by acronym or abbreviation. By assigning a different color to each stage, the chart conveys information graphically without the need for explanation. The legend is a shorthand reference; the cells are in small font and limited to four characters.
The second Plan sheet configuration is capacity thresholds. These apply to week and month buckets, when selected, to shade in three levels of gray those buckets with limited capacity. For example, a week with fewer than five workdays is lightly-shaded; fewer than four, medium shaded; fewer than one (full week down), dark shaded.
Shading of low-capacity buckets alerts the manager and team that there can be even lower capacity, if members take vacation when the work schedule is already short. These thresholds can be customized and are for display only. Scheduling of task end dates takes down days into consideration based on other settings.
Inputs
The first input is the project title, with some additional space provided for descriptive notes. These cells are white and lined, like a notepad. The cells just to the right of these input cells have an apostrophe character. This is invisible, yet occupies the cell so that text does not spill outside the input range.
The table of tasks has thirteen input columns. The first four columns are about the task; level, description, owner and status. Level [L] is a choice of 1 =main task or 2 =sub-task. A sub-task adds a decimal to the calculated task number, and highlights the chart with stripes. The decimal is added using the formula from the Startup Outline, and is limited to nine sub-tasks per task.
Task # =IF([@L],ROUNDDOWN(N(Above)+POWER(10,1-[@L]),[@L]-1),"")
Description and owner are free text, and status is a picklist of open, hold, and closed. Hold and closed turn the row gray, and closed turns the font a darker gray, still legible but different from hold.
The remaining nine columns are about the scheduling of the task, beginning with the start date, followed by a picklist column of indicators, labeled “et” for event type, then seven duration columns.
Column [et] combines two indicators; one to select continuous or discrete event, the other to select workday input or calendar day input.
Data Validation limits the entries and provides the picklist, but does not require an entry. If [et] is not filled, the default is cw = continuous in workdays. Most tasks will be defined in workdays, otherwise defining non-workdays is pointless. The default is set by IF:
TypeDay =IF(RIGHT(Project[@et],1)="c","c","w")
TypeEvent =IF(LEFT(Project[@et],1)="d","d","c")
A common use for calendar day input is for discrete events that occur on the same weekday every so many weeks, with the days entered as multiples of 7. It is important to choose [et] carefully for the intended effect and to avoid confusion.
Of the remaining seven columns, one of them must contain a duration for a continuous event to show on the chart. Normally a stand-alone task is entered in S1, but any of S1 to S7 can be used. The task can thus be given a different color by entering its duration in a different column. A task with two stages can be entered in any two columns, etc. However, if colors correspond to particular activities, then the same columns should be used for all tasks that share those activities.
With continuous stages, S1 is the duration of stage 1, S2 is the duration of stage 2, etc. With discrete events, the first one is on the start date, the second is S1 days later. Thus there can be eight discrete events on a single row, or a single event with no entries in S1 to S7. Discrete events are all the same color, so there is no reason not to use columns S1 to S7 from left to right.
The primary fields of a task record are description, start date and duration, and the chart will begin to form with nothing else. Understanding and using the nuances of stages, continuous vs. discrete events and workday vs. calendar day entry can be learned and adopted over time.
True project management software includes relationships between tasks. This is out of scope for this Gantt, other than the implicit finish/start relationship between stages. Relationship between tasks is by manual maintenance of successor start dates.
Controls
In addition to the task inputs, there are three controls; two checkboxes, and counting a three-button option group as one control. These are all aligned in a vertical stack just to the left of the period headers, above the End column. The linked cells are hidden within plain sight in the header.
The first checkbox “show all days” is relevant only when the Day bucket is selected; it displays down days with dark shading, otherwise, only workdays are displayed. The second checkbox “use all days”, turns off down days and shows what an unconstrained schedule would look like. This answers the question, “what if we work through weekends and holidays?” to give the earliest possible end date of tasks. This is meant to be a quick look, not a tactic. The strategy is to maintain realistic durations and downtimes, with tactical compromises.
The option group has three buttons lined up with the three buckets, Month, Week and Day. Changing the selection has the effect to expand or collapse the periods. With workdays display, that is, Day without “show all days”, there is no column shading because all columns are workdays. With Week or Month, shading takes effect once the low-capacity thresholds are crossed.
When period columns collapse into a larger bucket, the cell displays the stage color in effect at the end of the bucket. Stages that occur within the bucket are lost. This is the natural effect of zooming out.
Formatting
Use of Conditional Formatting here is unique in that there is no data in the chart cells. The chart appears to have colored shapes, which are cells formatted by the intersection of task and period data. Good Name Manager practices of naming convention and documentation is important to establish and audit the logic.
The logic must reference the stage milestone dates from the row and compare them to the period dates based on whichever bucket is selected. To accomplish this, relative names are defined for the current column period, PerThis, and the next column period, PerNext.
Stage Start
First, the task row’s milestone dates must be determined. The start date of each stage is required for both continuous and discrete events. Stage 1 begins on the manually-input task start date, and each successive stage’s start is offset from there by the previous stages’ duration.
TaskStart =Project[@Start]
TaskStartStage2 =WORKDAY.INTL(Project[@Start],Project[@S1], IF(OR(Uncon,TypeDay="c"),"0000000",Wkstring), IF(OR(Uncon,TypeDay="c"),0,Days[OffDays]))
Each stage accumulates previous stages, replacing Project[@S1] with SUM(Project[@[S1]:[S2]]), etc., and the last stage has an end date.
TaskEnd =IF(Project[@Start],WORKDAY.INTL(Project[@Start], SUM(Project[@[S1]:[S7]]),IF(OR(Uncon,TypeDay="c"),"0000000", Wkstring),IF(OR(Uncon,TypeDay="c"),0,Days[OffDays]))-IF(TypeEvent="d",0,1),"")
Continuous Stages
For continuous stages, the condition to highlight the stage must be true for the duration of the stage, that is, until the next stage starts. Stage 1 to 7 each has a Boolean formula to turn on its color. The condition relies on the intersection of two date inequalities.
The first inequality is when the next column period’s start date, PerNext, is greater than the start date of the row’s current or “nth” stage, TaskStartStage”n”. That means that “stage n has begun”. The first column that meets this condition is the stage start period, and the stage continues as long as the next condition is true.
The second inequality is when the current column period’s start date, PerThis, is less than the start date of the row’s next or “nth+1” stage, TaskStartStage”n+1”. That means that “stage n has not ended”, or more accurately, “stage n+1 has not begun”.
The intersection of these two inequalities is where “stage n has begun and stage n+1 has not begun”, which is the duration of stage n.
Two more criteria are added to the condition: that [Sn] is not blank or 0, and that [et] is set as a continuous event type. The final stage uses the task end date to turn off the condition.
ContinuousStage1 =AND(PerNext>TaskStart, PerThis<TaskStartStage2,Project[@S1]>0,TypeEvent="c") ContinuousStage2 =AND(PerNext>TaskStartStage2, PerThis<TaskStartStage3,Project[@S2]>0,TypeEvent="c") … ContinuousStage7 =AND(PerNext>TaskStartStage7, PerThis<=Project[@End],Project[@S7]>0,TypeEvent="c")
Discrete Events
For discrete events, the condition to highlight the chart must be true for a single period at a time, requiring a change to the logic. The first inequality is the same as “stage n has begun”. The second inequality is when the current column period’s start date, PerThis, is now less than or equal to the start date of the row’s current or “nth” stage, TaskStartStage”n”. There is no reference to the next, “n+1” stage. The condition thus means that the discrete event date occurs within the current period.
There are eight discrete event formulas, each with the two inequalities and the [et] discrete event criteria. For the chart, all discrete events are the same format, so a single formula combines the eight instances into one with OR.
DiscreteEvent1 =AND(PerNext>TaskStart, PerThis<=TaskStart,TypeEvent="d") DiscreteEvent2 =AND(PerNext>TaskStartStage2, PerThis<=TaskStartStage2,TypeEvent="d") ... DiscreteEvent8 =AND(PerNext>TaskEnd+1, PerThis<=TaskEnd,TypeEvent="d") DiscreteEvent =OR(DiscreteEvent1,DiscreteEvent2,...,DiscreteEvent8)
Other Formatting
The stages and events described above set cell formats. There are also full-row and full-column formats.
Column formats have five conditions in two types. The first type is the gray shading based on Month and Week capacity thresholds, as described in Configuration, with three levels from light gray to dark gray. The configuration cells are named like CT.. for capacity threshold. The formulas use PerIndex and are entered directly in Conditional Formatting. The dark formula also shades down days, when calendar days are displayed (Day with “show all days”).
Dark =CHOOSE(PerIndex,DownDay,0,PerDays<CTWk3,PerDays<CTMo3)
Medium =CHOOSE(PerIndex,0,0,PerDays<CTWk2,PerDays<CTMo2)
Light =CHOOSE(PerIndex,0,0,PerDays<CTWk1,PerDays<CTMo1)
These shades are patterns, so they can overlap and not obscure the task formats. They extend from the Weekday / Workdays row down all table rows.
The second type of column format is to show two key milestones, the current period and the project end. These use similar criteria to the discrete events, and set a light pattern blue for now and green for end. Vertical bars extend from Year through all period header rows and down the table rows. These two indicators are like goalposts showing the zone of action and what work remains to be done or caught up.
FormatEnd =AND(PerThis<=ProjectEnd,PerNext>ProjectEnd)
FormatNow =AND(PerThis<=TODAY(),PerNext>TODAY())
Row formats have several conditions. Some are applied across the entire table, some are separated between the text columns and the chart columns.
The first is for subtasks, which can be identified by level =2, or by =MOD(Project[@['#]],1). The task description is indented, and the chart is striped. Second, [Status] flags two formats that apply across the whole table. Status “hold” and “closed” both set the fill color gray, “closed” also sets the font gray. Finally, the implicit status “overdue” is determined and flags the row.
For text columns, if the status is not closed and the end date is in the past, all columns are set red font.
TaskLateText =AND(Project[@Status] <>"closed",Project[@End]<TODAY())
For the chart, the entire task, all stages are filled red.
TaskLateChart =AND(Project[@Status]<>"closed", Project[@Start]<PerNext,Project[@End]>=PerThis, Project[@End]<TODAY(),Project[@dur]>0)
Monitoring
Monitoring project progress is the whole point of having a Gantt Chart. The intent is to expose schedule status, delays and risks, and to support proactive adjustments to stay on track, or reactions to get back on track or to “recalculate” the plan.
The header has a status pane with four cells, each with a different color Data Bar format. At a glance these give a high-level view of where the project stands.
The first is the current date, displayed as “timeline”, with the Data Bar min and max set by the formula option to the project start and end dates. This is simply a progress bar of how far along the timeline the project is.
The second status cell has the number of closed tasks, =COUNTIF(Project [Status],"closed"). The Data Bar max is the count of all tasks with duration>0.
Third, current tasks are counted, defined as status=open, start before today, and end after today. These are the tasks that need to be worked on now. This bar’s max is the count of open tasks only.
Current tasks =COUNTIFS(Project[Start],"<"&TODAY(), Project[End],">"&TODAY(),Project[Status],"open")
The fourth status bar is tasks that are overdue, that is, in the past and still open. This bar is red, and goes from right to left, opposite the others for emphasis. The max is the count of open tasks.
Overdue tasks =COUNTIFS(Project[End],"<"&TODAY(), Project[Status],"open")
Maintaining overdue tasks is fundamental. If the task is completed, the status should be closed. If not, the end date should be rescheduled to a realistic date by extending the duration.
Some Gantt templates include a % complete, but in real life I have not seen that used. Some people manage their tasks fairly evenly, others wait until the day before they’re due. Project management is part science and part politics. It involves teasing out information and influencing behavior to accomplish the objective. The Gantt provides the transparent tool for the manager to monitor the big picture and “make the rounds” to get the details – “We have two weeks left in stage 3 of task xyz. How’s it going? Any help needed?” The successful manager is one who can convince members to be truthful, even when the obstacle is self-imposed.
Comments