How do I plot date against time?

[Guest]

I want to plot date against time. I have dates, the moonrise and moonset
times. I would like the date on the horizontal axis and a time scale on the
vertical axis, plotting the time of the moonrise and moonset as two different
series.
The data looks like this:
Date Rise Set
10/01/07 21:03 12:05
10/02/07 22:06 13:09
10/03/07 23:14 14:01
10/04/07 14:43
10/05/07 0:23 15:16
10/06/07 1:31 15:43
....
(sometimes rise/set are blank because the moon rose on the previous day;
there was no moonrise on a specific date)

I understand that the preferred format is an XY chart (lines)?
When I do it this way I get THREE series, of rise, set AND date.
Here are my questions:
1. How do I get the date back on the horizontal axis?
2. How do I get Time displayes on the vertical axis in more intervals,
preferably by hour?

I am using Excel 2007.

Thanks for your help!

Colleen

 

[Bernard Liengme]

1) You have column headers: Date, Rise, Set. Delete the first one )Date) and
make the chart. Excel will now treat the first column as the x-values rather
than the first y-value series.

2) Replace the blank by =NA() which displays as #N/A. The chart engine will
ignore this point

best wishes

 

[Guest]

How does this help me? I lose the date dimension, and seemingly plot one time
against another. I want to see two points (lines/series) for each date, with
date as the x-axis and time as the y-axis and two series plotted as different
colors (rise and set).
Somehow I got a chart to show the two time series on the y-axis, I just
wasn't able to get the date on the x-axis as I wished.

The note about #N/A is valuable - I didn't know that one.

 

[David Biddulph]

If you have deleted the header from your date column, and you now select the
range including the 3 columns (date and 2 sets of time) + the headers for
the two time columns (with the top left cell of this range being the blank
where you deleted the header of the date column), you should now be able to
Insert/ Chart/ XY again, and this time it should give the chart as you want
it.

An alternative, working from your existing chart with the 2 series, is to
edit the Source Data and adjust the X axis series for each of your 2 series
to show your date series.

The other thing to check, of course, is that you actually do have dates in
your cells in the date series, & not text.

 

[Del Cotter]

How does this help me? I lose the date dimension, and seemingly plot one time
against another.

No, don't delete the Date column, just leave its header row empty. The
Chart Wizard is a bit dim, and is looking for blank spaces in the top
left hand corner of the selected range, in order to be able to figure
which rows and columns you want to be the X axis and which to be the
series titles. For instance, this range:

A B
1 2 2
2 3 4

will produce 1,2 in the x-axis, and name the two data series "A" and
"B". If you give the 1,2 a header, the Wizard gets confused and tries to
plot all three as data series, as you discovered. Once you've
successfully built your chart, you can then give the first column a
label, you don't have to leave it blank.

This also works for more than one row and column. For instance:

X X
A B
2007 1 2 2
2007 2 3 4

Will create a chart where *both* the year column and the numbers 1,2 are
on the x axis, and the series are named XA and XB. This works for many
levels of row and column, depending on how extensive the blank rectangle
in the top left corner is. Again, they only need to be blank while
creating the chart. Afterwards you can fill them with whatever you like.

I want to see two points (lines/series) for each date, with
date as the x-axis and time as the y-axis and two series plotted as different
colors (rise and set).

Somehow I got a chart to show the two time series on the y-axis, I just
wasn't able to get the date on the x-axis as I wished.

The note about #N/A is valuable - I didn't know that one.

It's also okay to just leave the cell blank if it's not causing you any
trouble.

 

[Guest]

Bravo! So simple! Why did deleting that header work its magic?
Doesn't matter, it worked and I got what I wanted.

Thanks, all!

 

[Guest]

Del, thanks! I import data every day, so leaving a header *off* is just so
foreign to me that it felt just so wrong... Blind faith in your (pl)
knowledge and prior experience helped me past my own preconceptions.

Thanks, all!

 

[Del Cotter]

Del, thanks! I import data every day, so leaving a header *off* is just so
foreign to me that it felt just so wrong...

Me too, that's why I always put the header back in afterwards

 

[Bernard Liengme]

Deleting the HEADER was my first point. Did you try it?