Variation of a Gantt chart

[travelug]

Hi. I'm brand new here and definitely an Excel novice. Here's what I'm looking for:

Our local 911 center makes some statistical data available to each service they dispatch for in excel format. Here is an example of a snippet of data:

LOCATION    UNIT NAME       DISPATCHED           AVAILABLE        
Apple St    Ambulance 1     9/29/2010 00:10      9/29/2010 00:58
Pear St     Ambulance 7     9/29/2010 02:18      9/29/2010 03:02
Plum Alley  Ambulance 4     9/29/2010 02:40      9/29/2010 03:18
Peach Lane  Ambulance 1     9/29/2010 07:04      9/29/2010 07:58
Grape Way   Ambulance 2     9/29/2010 07:10      9/29/2010 08:03
Orange St   Ambulance 1     9/29/2010 13:18      9/29/2010 14:15

The data is sorted chronologically by time dispatched.

What we want to do is chart WHEN each unit is committed to an incident. A horizontal bar chart, where the entire length of a row on the chart would represent a 24 hour period (one day). For each UNIT NAME, a colored block/section representing when they were on a call and blank/white when they were available.

Using the example above, 'Ambulance 1' would have 3 blocks of time on the same bar, showing they were committed from 00:10 to 00:58, 07:04 to 07:58 and again from 13:18 to 14:15. Each UNIT NAME would have its own bar. In the above example, the chart would have a total of 4 bars, sorted by UNIT NAME.

Hopefully, I've sufficiently explained what I'm looking for. Please ask for any clarifications or omissions. travelug@yahoo.com

Thanks in advance for any/all help!

<!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:Author>dispatch</o:Author>
<o:LastAuthor>dispatch</o:LastAuthor>
<o:Revision>1</o:Revision>
<o:TotalTime>2</o:TotalTime>
<o:Created>2010-09-29T12:48:00Z</o:Created>
<o:LastSaved>2010-09-29T12:51:00Z</o:LastSaved>
<o:Pages>1</o:Pages>
<o:Words>236</o:Words>
<o:Characters>1349</o:Characters>
<o:Company>SVEMS</o:Company>
<o:Lines>11</o:Lines>
<o:Paragraphs>3</o:Paragraphs>
<o:CharactersWithSpaces>1582</o:CharactersWithSpaces>
<o:Version>11.9999</o:Version>
</o:DocumentProperties>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:WordDocument>
<w:SpellingState>Clean</w:SpellingState>
<w:GrammarState>Clean</w:GrammarState>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
</w:Compatibility>
<w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
</w:WordDocument>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" LatentStyleCount="156">
</w:LatentStyles>
</xml><![endif]-->

 

[Jon Peltier]

This is a Gantt chart. See Gantt Charts in Microsoft Excel, Advanced Gantt Charts in Microsoft Excel, and Gantt Chart Links for tutorials and links to other resources.

Set up your data with one row per unit. Put times for each unit chronologically left to right. Ambulance 1 has six times: dispatched, available, dispatched, available, dispatched, available; the other units have only two times: dispatched, available. This fills the first eight columns.

The next six columns are Available, On Call, Available, On Call, Available, On Call. The first Available column is merely a link to the first Dispatched time for each unit. The first On Call column is the first Available time minus the first Dispatched time, so it's the duration of the call. The next available column is the second Dispatched time minus the first Available time, so it's the gap between calls. Complete this logic for the rest of the calculated columns.

Select the last six columns and create a stacked bar chart. Hide the three Available series by formatting them with no border and no fill. Format all three On Call series with the same fill. Go to the source data dialog and add either location (the streets) of unit (the ambulances) as the Category (X axis) labels. Format the left hand axis so the categories are plotted in reverse order, and if desired have the value axis cross at the maximum category.

 

[Jon Peltier]

There's an even easier approach, which I've written about in my blog, in Gantt Chart for Repeated Tasks

 

[Jon Peltier]

Here's another way.

The data is shown below. The ambulance numbers in column B are really just the numbers in the cells (1, 7, 4, 1, 2, 1), formatted using a custom number format of

"Ambulance "0

The durations in column E are Available minus Dispatched, formatted as h:mm times.

Select column B of the data and insert an XY Scatter chart (first chart below). Note the Y axis shows Ambulance Y, not just Y.

Select the series in the chart. The series formula is

=SERIES(Sheet1!$B$1,,Sheet1!$B$2:$B$7,1)

Series name in cell B1, no X values, Y values in B2:B7. Click between the two commas, then select the dispatch times in the data, so the series formula becomes

=SERIES(Sheet1!$B$1,Sheet1!$C$2:$C$7,Sheet1!$B$2:$B$7,1)

Series name in cell B1, X values in C2:C7, Y values in B2:B7. Awesome. Now format the Y axis so the minimum is 40450 (which means 9/29/2010), the maximum is 40451 (9/30/2010), and the major unit is one hour, which is 1/24, or 0.416666666666666. This yields the second chart below.

Format the series so it uses no markers, but add error bars (upper chart below).

Delete the vertical error bars, and format the horizontal error bars to show positive only with no end caps, using the Custom Values option, and click the Specify Value button to use the Duration values in E2:E7. Format the error bars as thick colored lines (I used 6 points). This is shown in the bottom chart below.

You can hide Ambulance 0 and Ambulance 8 by using a custom number format of

[>7]"";[>0]"Ambulance "0;;

for the Y axis tick labels. If there are no Ambulances 3, 5, and 6, you need to do some more complicated manipulation of the data and axis formatting.