Plotting Bands for Task Overlaps

[gtslabs]

I am trying to create a chart with horizontal bands (similar to a bar
chart) but I need the color of the band to change with different dates.
For instance from:
01/01/2000 to 06/01/2000 plot Blue
03/01/2000 to 06/06/2000 plot Red
04/01/2000 to 07/06/2000 plot Yellow

My intention is to have 1 band with different colors based on a task
length. Then have multiple bands stacked so I can visualy check for any
overlaps in colors(tasks)

Right now my data is in 2 colums: Start Date Task Name


Thanks in advance
Steve

 

[Jon Peltier]

You need to make a Gantt chart. This is simply a stacked bar chart, where
the first bar is transparent (no border, no fill), then with another bar for
a task. Or in your case, multiple bars for multiple colors.



This article has some basic suggestions for creating Gantt charts in Excel:



<http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343>



Your chart source data would look like this:



Task Start Blue Red Yellow



The start date column is the same for all series. The other columns show the
elapsed days which should be formatted in that color, or zero if that color
shouldn't appear in the chart.



Your data as shown would start like this, not as above:



Task Name Start Date End Date

Blue Task 01/01/2000 06/01/2000

Red Task 03/01/2001 06/06/2000

Yellow Task 04/01/2000 07/06/2000



In the main table, use formulas to determine the elapsed time under the
appropriate color label. Fill the range A1:F4 like this:



Task Name Start Date End Date Blue Red Yellow

Blue Task 01/01/2000 06/01/2000

Red Task 03/01/2001 06/06/2000

Yellow Task 04/01/2000 07/06/2000



To simulate your color criteria, I populated L1:M4 with this lookup table:



1/1/2000 Blue

2/1/2000 Green

3/1/2000 Red

4/1/2000 Yellow



Then I selected D2:F4, with D2 as the active cell, and entered this formula:



=IF(VLOOKUP($B2,$L$1:$M$4,2)=D$1,$C2-$B2,0)



Then I held down CTRL while pressing Enter to enter it into all selected
cells. The resulting range looks like this:



Task Name Start Date End Date Blue Red Yellow

Blue Task 01/01/2000 06/01/2000 152 0 0

Red Task 03/01/2001 06/06/2000 0 97 0

Yellow Task 04/01/2000 07/06/2000 0 0 96

Select A1:B4, then hold CTRL while selecting D1:F4, so both regions are
selected. Start the chart wizard, and select a stacked horizontal bar chart.
Now simply format the chart to get the colors you want.


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______