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
3. Expose only the Late (number 1) field on the Gantt chart.
Sample results are shown in Figure 2.
Figure 2: Sample results.
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.