This hack features a quick way to indicate task timelines in Gantt charts. After populating an Excel sheet with tasks, activities, owners, start dates and end dates, the work of coloring out cells to provide a visual comparison of task timelines is manually done and tends to be time consuming, not to mention error-prone and burdensome to update in the event that changes are made to the project timelines. Using formulas in conditional formatting, this task can be reduced to a few minutes. Also, with conditional formatting, a “currently here” highlight bar can be added to the Gantt chart to quickly guide user’s view to the tasks scheduled to start or end on the present day/week.

Step 1: Set up your project plan as normal, with separate columns for start and end date. Ensure the data in the start and end date columns are in date format.

Step 2: Set up the columns to show dates till the end of your project. Also ensure that the date headers of these columns are set up in date format

Step 3: Select the entire area of the date columns, select “Conditional Formatting” and “Manage Rules” to add a new condition for formatting. Input the following formula: =AND($E9<=L$8,$G9>=L$8)

This formula checks each cell by row in the dates area of the Gantt chart to confirm if they fall on or between the start (column E) and end (column G) dates of the task. The cell reference type used in this formula (locked for rows and unlocked for columns for the date headers, unlocked for rows and locked for columns for the start and end dates) also ensure that the conditional formatting rule is checked systematically, by row. The result should be similar to what is below:

Step 4: To create a highlight bar, create a cell that always references today’s date by typing =TODAY() (in $E$6 in our example) , and then reference this cell in a new conditional formatting rule for only the date area headers as shown below:

Create a second and third rule to add left and right borders to the middle date area cells and a bottom border to the last date area cells respectively.