Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can you have 2 date ranges in one row on an Excel Gantt chart?
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can you have 2 date ranges in one row on an Excel Gantt chart?
On Fri, 7 Dec 2007, in microsoft.public.excel.charting,
KAREN_W said: 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/p...cle.asp?ID=533 -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can you have 2 date ranges in one row on an Excel Gantt chart?
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! -- Karen W. "Del Cotter" wrote: On Fri, 7 Dec 2007, in microsoft.public.excel.charting, KAREN_W said: 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/p...cle.asp?ID=533 -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can you have 2 date ranges in one row on an Excel Gantt chart?
On Sat, 8 Dec 2007, in microsoft.public.excel.charting,
KAREN_W said: 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. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can you have 2 date ranges in one row on an Excel Gantt chart?
Del - I can not believe it! I should have posted months ago. Thank you so
much!!! -- Karen W. "Del Cotter" wrote: On Sat, 8 Dec 2007, in microsoft.public.excel.charting, KAREN_W said: 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. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can you have 2 date ranges in one row on an Excel Gantt chart?
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. "KAREN_W" wrote: Del - I can not believe it! I should have posted months ago. Thank you so much!!! -- Karen W. "Del Cotter" wrote: On Sat, 8 Dec 2007, in microsoft.public.excel.charting, KAREN_W said: 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. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can you have 2 date ranges in one row on an Excel Gantt chart?
I figured out the width size on the 1st and last bus. Still working on
centering the dates and overlapping dates. -- Karen W. "KAREN_W" wrote: 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. "KAREN_W" wrote: Del - I can not believe it! I should have posted months ago. Thank you so much!!! -- Karen W. "Del Cotter" wrote: On Sat, 8 Dec 2007, in microsoft.public.excel.charting, KAREN_W said: 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. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can you have 2 date ranges in one row on an Excel Gantt chart?
On Sat, 8 Dec 2007, in microsoft.public.excel.charting,
KAREN_W said: 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. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Can you have 2 date ranges in one row on an Excel Gantt chart?
Del - Thanks this did the trick! Thank you so much.
-- Karen W. "Del Cotter" wrote: On Sat, 8 Dec 2007, in microsoft.public.excel.charting, KAREN_W said: 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. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Gantt chart using Excel (Video) | Excel Discussion (Misc queries) | |||
how do I create a gantt chart in excel | Charts and Charting in Excel | |||
How do I create a Gantt chart using Excel 2003? | Excel Discussion (Misc queries) | |||
gantt chart in excel | Charts and Charting in Excel | |||
2 or more layers on a Gantt Chart(Excel 2002) | Charts and Charting in Excel |