Recently, a colleague asked how he could tell at a glance which overdue tasks were at least 50% complete and which were less than 50% complete.

  • If <50% complete, show red alert (red circle icon)
  • If as least 50% complete, show yellow alert (yellow warning icon)

Now perhaps there’s a more elegant way to do this, but using the brute force method, here’s how I solved it.

1. Customize four fields [Number 1], [Number 2], [Number 3] and [Flag 1] as follows.

Late (field number1). The number fields are used to identify specific conditions, which are then aggregated into the Number 1 or “Late” field, which is exposed on the Gantt view in Figure 2.

Formula: [Number2] Or [Number3] Or [Finish]>Now() Or [Flag1]

>49% (field number2) is used to detect if a task is late and at least 50% complete:

Formula: [Finish]<[Current Date] And [% Complete]>49 And [% Complete]<100

<50% (field number3) is used to detect if a task is late and less than 50% complete:

Formula [Finish]<[Current Date] And [% Complete]<50

Flag 1 is used to detect if a task is complete:

Formula: [% Complete]=100

2. Set graphical Indicators for field [Number 1] (renamed “Late”) as shown in Figure 1.

Figure 1: Graphical indicators
Sorting out Overdue Tasks from Waaaayyyy Overdue Tasks
3. Expose only the Late (number 1) field on the Gantt chart.

Sample results are shown in Figure 2.

Figure 2: Sample results.
Sorting out Overdue Tasks from Waaaayyyy Overdue Tasks
A note on logic: Field number 1 uses the previous three fields to capture all possible conditions and produce an indicator based on the values found in each of them. Field 1 says, look at all these fields to see if this item is not yet due. Then if it is not yet due, do nothing. If field Number 3 is true, show a red dot. If Field Number 2 is true, show a yellow diamond.