Excel: Gantt Chart With Different Colors Before and After a Given Date

On the internet you can find many examples of Excel Gantt charts, either created as a graph or using conditional formatting and how to build them.

My example has some additions to the graph type of the Gantt chart:

• A date line (the black vertical line).
• Different colors for the task or part of a task before and after a specific date (in this case July 31).

Here are the data, from A1:C27:

Task	Start Date	Duration in days
Task 1	3-6-2013	1
Task 2	4-6-2013	1
Task 3	4-6-2013	5
Task 4	11-6-2013	1
Task 5	11-6-2013	12
Task 6	25-6-2013	3
Task 7	28-6-2013	1
Task 8	28-6-2013	4
Task 9	4-7-2013	3
Task 10	9-7-2013	1
Task 11	10-7-2013	3
Task 12	15-7-2013	3
Task 13	18-7-2013	1
Task 14	21-7-2013	5
Task 15	24-7-2013	7
Task 16	27-7-2013	4
Task 17	30-7-2013	3
Task 18	2-8-2013	6
Task 19	5-8-2013	8
Task 20	8-8-2013	9
Task 21	11-8-2013	10
Task 22	14-8-2013	12
Task 23	17-8-2013	14
Task 24	20-8-2013	15
Task 25	23-8-2013	13
Task 26	26-8-2013	5

The date is in cell I1: 31-7-2013.

And there are a lot of formula's in D1:H27. I hid the columns D:H.

You can also download the file ganttchart.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing