Gantt Chart

  • Thread starter Thread starter Salam
  • Start date Start date
S

Salam

I Tried to create a gantt chart for leave schedule each
person allowed to have two leave period in the year , how
Can I display a chart showing the person name on the
vertical axis and date in the horizontal axis. Note: I do
not want to deplicate person names
A1:Name B1:StartLeave1 C1:EndLeave1 D1:StartLeave2
E1:EndLeave2
A2:John B2:2/3/04 C2:5/4/04 D2:2/6/04 E2:2/7/04 (d/m/y
format)
Thanks and Happy New year
 
You need to plot differences in date in a gantt chart, which is a
floating bar chart.

For your data, insert some columns for the chart-ready data. Here are
the column assignments:

A: Name
B: Start1
C: End1
D: Start2
E: End2
F: StartLeave1 (your data)
G: EndLeave1
H: StartLeave2
I: EndLeave2

Put these names into row 1:
A1: leave blank
B1, D1: anything
C1: Leave 1
E1: Leave 2
F1-I1: anything

Put these formulas into row 2:
B2: =F2
C2: =G2-F2
D2: =H2-G2
E2: =I2-H2

Fill these down as far as you have names in column A. Now make a stacked
bar chart with the data in columns A through E, with series in columns.
The blank in A1 tells Excel that row 1 contains series names and column
A contains category labels. You may want to format the vertical axis so
the categories are in reverse order and the value axis crosses at the
maximum category. Also format the first and third bar series to be
invisible: double click on each, click on the Patterns tab, and choose
None for Border and for Fill.

- Jon
 
Thanks for your suggestion I implemented straightaway,
however I noticed that it works fine as long as (H2-G2) is
positive if negative then it will not work, I did If
statement to swap the contents then it works partially i.e
the problem of coloring the bar has arised and also I
noticed that when Leave2 partially ovelap with Leave1 not
correct gantt chart is presented. Reason for overlapping
that if someone going for annual leave and part of it is
training. Please advice.
 
You could make your formulas smarter. This one gives you the difference,
no matter which is larger:

=IF(H2>G2,H2-G2,G2-H2)

Also you need to decide how you want overlapping bars to be shown in the
chart, then adjust the formulas accordingly.

- Jon
 
Back
Top