chart dates and times (excel 2007)

[chuck]

Hi All,
I have 2 columns of data. One is a series of dates. The second is a
series of times when a particular event happened on that date. To
make it troublesome on a few of the dates the event didn't happen.

I have been asked to create a chart (line) with dates on x-axis and
event times on y-axis where the y-axis is measured off at 15 minute
intervals.

To be frank I didn't even know where to begin.

I tried selecting both columns and then INSERT a Line chart but it
gave me two horizontal lines parallel to each other with the first
line running along the x-axis and that too starting after the last
date when the time value was null.
So I removed the rows that had no value in the time column and
recreated the line chart- same result, only the lines were 'longer'
i.e. all the values got mapped but still one horizontal line running
along the x-axis and another parallel to it.

So obviously I am going about it all wrong- any pointers on where to
begin?

Thanks!

 

[Bernard Liengme]

Use a table like this
<blank> time
1/1/2009 4:30 AM
1/2/2009 10:15 AM
1/3/2008 =NA()
1/4/2009 6:10 AM

Row 1: if you put the label "date" in the cell marked <blank>, Excel will be
too helpful and think that this column is any data series rather than your
x-values

Row 4: type the formula =NA() when the event did not occur; this will
display as #N/A.
This will be ignored by the chart engine while a blank would be treated as
zero

best wishes

 

[chuck]

Use a table like this
<blank>       time
1/1/2009      4:30 AM
1/2/2009      10:15 AM
1/3/2008      =NA()
1/4/2009      6:10 AM

Row 1: if you put the label "date" in the cell marked <blank>, Excel willbe
too helpful and think that this column is any data series rather than your
x-values

Row 4: type the formula =NA() when the event did not occur; this will
display as #N/A.
This will be ignored by the chart engine while a blank would be treated as
zero

best wishes

Thanks Bernard- that worked for me. I need to tweak the increments on
the y-axis (for some reason it started off with an hour and 12 minutes
with the values I had) but I think I can monkey around with major-axis
and minor-axis and so on to make it work the way I want.

 

[Bernard Liengme]

Sounds good.