chart with date/time values

  • Thread starter Thread starter Gaetano D'Aquila
  • Start date Start date
G

Gaetano D'Aquila

Hi,
I would to realize with excel an X/Y line diagram where on the X axis I
would put a date/time value.
There is anyone that know how to do this?
We suppose of having to represent these data
X Y
01/05/2004 10.00 1
01/05/2004 10.30 2
01/05/2004 18.00 3

I Succeed to correctly make the diagram but the space between the values of
X axis is equal for all values although the distances between values are not
the same (30 minutes, 8 hours etc). There is anyone that can help me?
Thank you
G
 
If Excel understands your date/time as a value, then your chart should
reflect the duration between points. I don't know what your regional
settings are, and whether a period is interpreted as a time separator.
On my computer, I enter 10:00 for ten o'clock in the morning. If Excel
does not recognize 10.00 as 10:00, it will consider the entire field as
a text value, and it will assume they are not numeric, and it will use
hidden values of 1, 2, 3 for the data.

If you have made a LINE chart instead of a SCATTER chart, one of two
things can go wrong. One is that the date and time are recognized as
date and time, and being all on the same day, Excel depicts the points
on a vertical line. That's just how Excel does times on a line chart.

The other problem with a line chart is that it might simply interpret
the date-time values as category labels, as in the scatter chart above
that used 1, 2, 3.

So the important points are: make sure Excel knows it's a date and time,
and make sure it is in fact a scatter chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Hi Jon,
In the meanwhile I have see your web-site and I read that excel considerate
the date/time value as an integer without considerating hours (on a
date-time axis). This is my problem!!!
Does not exist some method in order to make so that Excel recognizes the
hours?

Thank you
 
Gaetano -

That is a problem, and it is not obvious, as you have proved. You can
show the complete date-time values (integer plus fraction) if you make
an XY Scatter chart instead of a Line chart. The difference between them
is not in formatting: you can have a line or no line, markers or no
markers, in either chart type. The difference is in the treatment of X
Values.

When scaling the X axis, you need to put the appropriate values of date
and time in the min, max, and spacing entry boxes in the Scale tab.
However, if you don't know offhand that 5/5/2004 8:00 (the approximate
time that I'm writing this) is known by Excel as 38112.33333, simply
enter 5/5/2004 8:00. Excel will convert this to 38112.33333
automagically. For the spacing values, enter hours and minutes like this
2:00 (for a two hour tick spacing).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Hi Jon,
Hi have tried your suggestion but does not work on my sistem...
If you want I can send you a sample .xls (few kb) file with more information
about my proceedings.
Thank you.
 
Back
Top