Ah, now you know what I mean, make the formulas smarter. Applying the
dumb formulas, you get a range like this:
Start Leave Gap Train LvStart LvEnd TrStart TrEnd
Salam 2/2/04 30 63 31 2/2/04 3/3/04 5/5/04 5/6/04
John 5/2/04 28 -62 8 5/2/04 4/3/04 2/1/04 10/1/04
Peter 6/6/04 93 -68 19 6/6/04 7/9/04 1/7/04 20/7/04
The negatives are a sign that my assumptions were too simplistic. Your
case has training occurring before, during, or after leave. So our
formulas must be smarter, to allow for leave, training, leave+training,
or a gap, and with leave before or after training.
With LeaveStart, LeaveEnd, TrainingStart, and TrainingEnd dates in
columns I through L, set up this range in A1:H4:
Start Leave Train Gap Both Leave Train
Salam
John
Peter
(Yes, I know there are two columns each for Leave and Train.) Keep cell
A1 blank, so Excel makes the chart properly.
Put these formulas where indicated:
B2: =MIN(I2,K2)
C2: =IF(I2<K2,MIN(K2,J2)-I2,0)
D2: =IF(K2<I2,MIN(L2,I2)-K2,0)
E2: =IF(MIN(J2,L2)<MAX(I2,K2),MAX(I2,K2)-MIN(J2,L2),0)
F2: =IF(MIN(J2,L2)>MAX(I2,K2),MIN(J2,L2)-MAX(I2,K2),0)
G2: =IF(J2>L2,J2-MAX(L2,I2),0)
H2: =IF(L2>J2,L2-MAX(J2,K2),0)
Fill these down as far as you have names in column A. Make a stacked bar
chart from columns A through H, with the series in columns. Format the
Start and Gap series to be invisible (border and area both None), format
both Leave series the same, format both Training series the same. Remove
extraneous legend entries by selecting the legend, then selecting the
text of the entry, and pressing Delete (don't select the legend key, the
little colored square, or you'll delete the series).
You now have one color each for leave and training, and a third for
overlaps. I don't think this is going to be easily understood at first
glance. It would be better to have two bars for each name, one for
leave, and one for training. The trick to a chart like this is in
staggering of the data. The technique is shown on Stephen Bullen's site
(
http://bmsltd.co.uk) and jazzed up a little by Bernard Liengme
(
http://www.stfx.ca/people/bliengme/ExcelTips/Columns.htm).
Set up your data like this:
Start Leave Train LvStart LvEnd TrStart TrEnd
Blank Row
Salam 2/2/04 30 2/2/04 3/3/04
5/5/04 31 5/5/04 5/6/04
Blank Row
John 5/2/04 28 5/2/04 4/3/04
2/1/04 8 2/1/04 10/1/04
Blank Row
Peter 6/6/04 93 6/6/04 7/9/04
1/7/04 19 1/7/04 20/7/04
Blank Row
There are two rows of data for each name, plus blank rows which space
the data in the chart. You need cell A1 to be blank, and some dummy
thing in cell A2, at least until after the chart is made. The cells
under Leave and Train have the appropriate durations. Make a stacked bar
chart from columns A to D, with series by column. Format the Start
series to be invisible. Now, any overlap is obvious, as a span within
which you have both bars (more obvious than translating colors into one
or both activities).
- Jon
