Can you have 2 date ranges in one row on an Excel Gantt chart?

[KAREN_W]

Is it possible to do a gantt chart in excel with the following criteria:

Bus Start Date Duration End Date Start Date Duration End Date
L180 9/1/2007 5 9/6/2007 9/15/2007 6
9/21/07
L220 9/15/2007 4 9/19/2007 9/25/2007 4 9/29/07

I am able to get the first set of start and end dates to chart, but not the
2nd series.
Any help would be appreciated. I've been trying this for months.

Karen

 

[Del Cotter]

Is it possible to do a gantt chart in excel with the following criteria:

Bus Start Date Duration End Date Start Date Duration End Date
L180 9/1/2007 5 9/6/2007 9/15/2007 6
L220 9/15/2007 4 9/19/2007 9/25/2007 4 9/29/07

I am able to get the first set of start and end dates to chart, but not the
2nd series.
Any help would be appreciated. I've been trying this for months.

Provided the second date range always starts on or before the first
range ends, your data should look like this

Bus Start1 Duration1 Gap Duration2
L180 09/01/07 5 9 6
L220 09/15/07 4 6 4

Make a Stacked Bar Chart from this table. Eliminate the word "Bus" from
your top left hand corner when you run the Chart Wizard: you can put it
back afterwards. Use the data you supplied above to make a formula to
calculate the gaps. Format the first and third bars to be invisible,
leaving only the second and fourth floating in the Gantt chart.

For a completely different approach, see Jon Peltier's Stacked Area
Chart example at:

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=533

 

[KAREN_W]

Del, Thank you for posting...My only concern is your comment that
"provided the second date range always starts on or before the first
range ends...This is not always the case. I often have a bus touring that
is sitting for a couple days. I need my chart to show that it is off. And
as I'm typing this, I may have a line showing 'off' days, so you are probably
on to something!

It is not uncommon for 1 bus (including the range of off days) to have up to
5 or 6 date ranges. Example: 9/1-9/10 (tour); 9/11-9/12 (off); 9/13-9/20
(tour); 9/20-9/24 (over lap days and deadhead bus to another location);
9/25-9/30 (tour).

Thank you so much and I will definitely try this. You are giving me hope.
I have been trying this since September!

 

[Del Cotter]

Del, Thank you for posting...My only concern is your comment that
"provided the second date range always starts on or before the first
range ends...This is not always the case. I often have a bus touring that
is sitting for a couple days. I need my chart to show that it is off.

Sorry, I made a mistake. I meant of course "on or after". The idea is
that you can have the days off, but you can't have overlaps.

The column I called "Gap" is your "Off", so:

Bus Start1 Duration1 Off Duration2
L180 09/01/07 5 9 6
L220 09/15/07 4 6 4

In this example, bus L180 is off for 9 days and bus L220 is off for 6
days. Try it and see if it works for you.

 

[KAREN_W]

Del - I can not believe it! I should have posted months ago. Thank you so
much!!!

 

[KAREN_W]

Is there any way to show an overlap with a / symbol on the overlapping day
for a particular bus?

Also, I am tweaking the chart and it is looking good. The only things I
have not figured out yet is that the first bus and last bus on the chart are
1/2 width size vs the other 8 buses. Also, trying to figure out how to
center the dates between a grid vs off to the left of each grid. I'll keep
working on it.

 

[KAREN_W]

I figured out the width size on the 1st and last bus. Still working on
centering the dates and overlapping dates.

 

[Del Cotter]

Is there any way to show an overlap with a / symbol on the overlapping day
for a particular bus?

I don't know what you mean by "with a / symbol". You can show an overlap
with another colour of bar (maybe a darker version of the same colour,
to imply double service). The calculations have be a little more
complicated in order to do this.

Start1 Single1 Off Overlap Single2
L180 09/01/07 5 9 0 6
L220 09/15/07 4 6 0 4

In this scheme, "Start1" and "Off" are invisible bars, "Single1" and
"Single2" are light colours, and "Overlap" is a dark colour. The
formulae look like this:

Bus Start1 Single1 Off Overlap Single2
=B3 =C3 =MIN(E3,F3)-C3 =MAX(0,F3-E3) =MAX(0,E3-F3)
=H3-MAX(F3,E3)
=B4 =C4 =MIN(E4,F4)-C4 =MAX(0,F4-E4) =MAX(0,E4-F4)
=H4-MAX(F4,E4)

An example may help:

http://www.branta.demon.co.uk/excel/busgantt1.xls

I've included a spinner control that changes the duration of the first
bus on route L180. See what happens when you click on it so that the gap
shrinks to zero and then the buses overlap? The Off column becomes zero
and stays that way, and the Overlap column switches from zero to a
positive number of days. Meanwhile, the Single1 bar stops growing and
the Single2 bar starts shrinking.

This system breaks if Single1 completely overruns Single2, so if you
have to account for that happening, you'll need to construct a scheme
even more complicated, or abandon the idea for some other method like
Pivot tables.

 

[KAREN_W]

Del - Thanks this did the trick! Thank you so much.