Time-based floating bar chart

[Mike]

I have a problem similar to "Kaixi".

I have data for elapsed times of backups, and I'm trying
to chart the beginning and ending time of the backups for
each server. The only problem is that each server may
have more than one backup job, so I need to chart the
elapsed time from the earliest start time to the latest
end time, with gaps shown where there is no activity.
The data looks like this:

server start-time end-time
abc 08:00 08:30
abc 08:00 08:45
abc 09:00 09:30
def 09:00 09:10
def 09:30 09:45
etc....

I'm using a floating-bar chart which works pretty well
for the data, except that each backup job entry is on a
different bar. What I'd like to do is have a non-
continuous bar that shows the elapsed time for each
server on a single bar:

|
|abc ---------- ---------
|
|def -- ----
|
|
|
| 07:00 08:00 09:00
--------------------------------------------------------

I'm open to "massaging" the data to make it fit the chart
type, and I've played with Pivot charts and tables to
that end without much success.

Any advice is greatly appreciated.

 

[Jon Peltier]

Mike -

This is like a gantt chart, and I have a list of links on my site for
this (my own tutorial is woefully incomplete):

http://peltiertech.com/Excel/Charts/GanttLinks.html

Basically, there are two approaches. One uses stacked bar charts, and
the other uses worksheet formulas and conditional formatting, to build
the bars out of colored cells. In general I prefer the former approach,
but this might be an exception.

Basically, for a floating bar chart, you need two stacked bar series: an
invisible series that reaches from the origin of the chart (the vertical
axis) to the starting point, and a visible series that shows the
duration between start and end. To display multiple bars for each
category, you need a pair of series per bar, or six series to show three
bars and two gaps. Only the first invisible bar reaches back to the
beginning of your axis; the other five show durations. You are likely to
have one range in the sheet with on and off times, and another that uses
formulas to determine the durations. Something like this:

Times
Server Start1 Finish1 Start2 Finish2 Start3 Finish3
abc 08:00 08:30 08:45 09:00 09:15 09:45
....

Durations
Server Start1 Finish1 Start2 Finish2 Start3 Finish3
abc 08:00 00:30 00:15 00:15 00:15 00:30
....

This should get you started; for further help, check those links, or
post back with specific problems.

- Jon

 

[jon]

Sorry it took me so long to get back to you. This sounds
like it might work for me. I'll give it a try.

Thanks for the advice!