ExcelBanter - Highlighting Operating Hours

On Fri, 17 Aug 2007, in microsoft.public.excel.charting,
Rebecca R said:

"Andy Pope" wrote:
Rather than shapes use a combination chart. An additional series plotted as
column chart is probably the simplest solution.
See Jon's example of vertical bands which should give you a starting point.

The x-axis is dates & times. The y-axis is temperature values. I don't see
how I can use a stacked bar graph for this.

Don't worry, the horizontal bar chart will have its own axes separate
from the scatter chart. So its "y-axis" (actually its x-axis) can be one
category, and it "x-axis" will be the hours the office is open and
closed. Study Jon's example, and adapt it to your case; I'm confident it
will work for you.

To see how this can make a time marker for your office opening and
closing, see this other example of Jon's, using the same technique:

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

It's like your office "open" and "closed" hours are two tasks in a Gantt
chart, only we really want them to fill the background, to make a field
on which the scatter markers are displayed.

But if it really bothers you, there is an alternative. Instead of a
stacked horizontal bar chart, use a stacked area chart with a
"Time-scale" x-axis instead. It will still have to have its own x and y
axes separate from the scatter chart, but at least it will be oriented
the same way for you instead of at right angles.

Whereas the horizontal stack bar design will have as many series as
there are cycles of opening and closing, there will be a lot of series
in that method, which you would delete from the legend if you are using
a legend. The stacked area method has only two series, one for "open"
and one for "closed" (it could have only one, with the background
standing for the alternative). In this scheme you would make one series
maximum when the office is open and zero when it is closed, and vice
versa for the other. Taking advantage of the Time-scales ability to do
vertical lines, you would repeat a time at opening and closing time,
like this:

Open Closed
22 100% 0
22 0 100%
23 0 100%
23 100% 0

At time "22" the "Open" area goes vertically down from 100% to 0, and
then at time "23" it goes vertically up again from 0 to 100%, and vice
versa for the "Closed" area, making rectangles of open and closed color
that you control from the spreadsheet, instead of having to position
graphical rectangles.

Since your hours are fractions of a day, you won't be able to use the
"Time-scale" in the obvious way, because it doesn't do hours. Just let
the "days" stand for hours or minutes and it will work fine.

--
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.