1/8/2011

I received an e-mail request from a real doctor who wanted help developing a chart to show the schedule for an operating room. The doctor sent along this  hand sketch. He wanted to be able to show time of operation from 6:00 Am till 8:00 PM .

I was happy to develop an Excel chart to show the daily schedule for an operating room.

Chart Planning

I wanted the chart to have bars for the operation, showing the start and end of the operation schedule.

I started with an XY chart. I figured that the Y Axis would be the sequence number for the operation. Since we wouldn't need this number, we could just turn off the Y axis labels.

The X axis had to show the time of day, from 6 Am to 8:00 PM.

The horizontal bars could be X error bars, starting at the start time and ending at the start time + duration (hours, minutes).

Organizing the Data

The data for the chart is shown to the right. Column A contains the Operation number, col. B contains the Start time and col. C contains the Duration (hrs:min), col. D contains a description for the operation. 

Making the Chart

I used the Chart Wizard to make the chart. I selected XY chart as my chart type. I specified the data range for my series, with B3:B7 for x data and A3:A7 for Y data.

My basic chart XY chart connected the start times. I cleaned up the chart by removing the line and removing the background and legend and subduing the grid lines.

My next step was to add the horizontal bars for the scheduled period. To do this, I added X error bars as shown below.

I added labels to the bars with J Walk's Chart Tools add-in, available on his site http://j-walk.com/ss/excel/files/charttools.htm.

Final Chart 

Here's the final chart after some clean up and fine tuning.

Here's a link to the Excel file if you'd like to check out this example in more detail. file