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

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.

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


"Zie bericht" wrote in message
ll.nl...
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

Hi Bernard: that cell is not present in the actual chart.

Ron


Bernard Liengme wrote:
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.


"Zie bericht" wrote in message
ll.nl...
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

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 Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Zie bericht wrote:

Hi Bernard: that cell is not present in the actual chart.

Ron


Bernard Liengme wrote:

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.


"Zie bericht" wrote in message
4all.nl...

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

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

Jon Peltier wrote:
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 Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Zie bericht wrote:

Hi Bernard: that cell is not present in the actual chart.

Ron


Bernard Liengme wrote:

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.


"Zie bericht" wrote in message
ll.nl...

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

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


Zie bericht wrote:
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

Jon Peltier wrote:
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 Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Zie bericht wrote:

Hi Bernard: that cell is not present in the actual chart.

Ron


Bernard Liengme wrote:

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.


"Zie bericht" wrote in message
ll.nl...

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

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Zie bericht wrote:

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

Jon Peltier wrote:

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 Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Zie bericht wrote:


Hi Bernard: that cell is not present in the actual chart.

Ron


Bernard Liengme wrote:


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.


"Zie bericht" wrote in message
. xs4all.nl...


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