Problem: Charting dates (X) against time values (Y)

  • Thread starter Thread starter Zie bericht
  • Start date Start date
Z

Zie bericht

Hi all,

I ran into this problem I do not seem to be able to solve.

I work on an app that exports data to Excel. All data has dates (or hours
within a date) as the X-axis. Some of the Y-axis data is numerical (no
problem at all), some is completely "time" (Like: hours worked at a certain
site).

I want to be able to automatically generate an excel chart (as the data
comes from a different app and may contain several types of data).

When data is of the form:

Date time (Y-axis)
1-1-2001 9:00
1-2-2001 7:00

Strange charts appear, but not what I want it to be.

Does some one have a solution to this?

Thanks!

Ron
The Netherlands
 
Need more info.
What do you mean by 'strange charts'
Are the dates being recognized as dates?
Try deleting the cell with "Date" and then make the chart.
 
Ron -

If the values are recognized as numerical (date and time are numerical)
and not as text, put a caption in the cell above the time values, and
keep the cell above the dates blank:

Time
1-1-2001 9:00
1-2-2001 7:00

Select the range, including the blank cell and the label, and run the
chart wizard. You could make a Line or XY Scatter chart, which will have
the dates along the bottom axis and time up the left side axis.

If your X data must show different times within dates, you need to make
an XY Scatter chart. A line chart allows perhaps more flexibility in
choosing the axis parameters, but every time during a given date is
plotted as if it were midnight at the beginning of that date.

- Jon
 
Jon,

Thanks very much for the input, it is very appreciated! Your answer does
prove in the right direction. Problem is that I need a caption above the
dates as well (for my end users to understand the graph). What is the logic
behind the solution you provided?

Unrelated: is there a way to force Excel to show decent times on the Y-axis?

Ron
 
Ps. I do know how to change Y-axis values by hand. Just curious if I can
teach Excel to do that for all time types values.

Ron
 
Hi Ron -

You can insert a caption after the chart is made. I leave the cell blank
because when making the chart, Excel tries to make sense of the blank,
and guesses correctly that I want the column below the blank as X values
and the row to the right as series names (in the legend). You could also
use the source data step of the chart wizard to get the X and Y values
in the right place, but that's too much like work.

To get decent times, enter the actual time values you want in actual
time formats. Enter 6:00 to start at 6 o'clock (or six hours), enter
1:00 to get a tick spacing of one hour. Excel wants a number, but it
will convert your entry into the corresponding fraction.

- Jon
 
Back
Top