Discretionary X-axis (for date & time)
 

Hi,

Im trying to plot some stock price data in a graph but running into the
following problem:

The data i want to plot is non continuous, i.e. the datetime should stop at
5.30 pm and continu on 9 pm the next day. When i try to do that with a
scatter or line graph, it does not work.

I guess one workaround would be to make an additional series that translates
the date & time to e.g. 1, 2, 3 ,etc leaving out the dates& times i dont
want to show. Tried this and it seems to work except for the fact that the
interval between my datapoints isnt always the same. This makes the scaling
of the graph not correct. (eg time between 2 points is sometimes 5 mins,
and sometime only 1 minute)

any suggestions?

Discretionary X-axis (for date & time)
 

Try a different transformation. You want to adjust the fractional part so
that 0 is at 9 am and 1 is at 5:30 pm.

With your original date-time in column A, starting in A2, insert a column,
the new B column, and in B2 use this formula:

=INT(A2)+(A2-INT(A2)-TIME(9,0,0))/(TIME(17,30,0)-TIME(9,0,0))

and fill this down the column. Use column B as the new X value in your XY
chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Frank" wrote in message
...
Hi,

Im trying to plot some stock price data in a graph but running into the
following problem:

The data i want to plot is non continuous, i.e. the datetime should stop
at 5.30 pm and continu on 9 pm the next day. When i try to do that with a
scatter or line graph, it does not work.

I guess one workaround would be to make an additional series that
translates the date & time to e.g. 1, 2, 3 ,etc leaving out the dates&
times i dont want to show. Tried this and it seems to work except for the
fact that the interval between my datapoints isnt always the same. This
makes the scaling of the graph not correct. (eg time between 2 points is
sometimes 5 mins, and sometime only 1 minute)

any suggestions?

Discretionary X-axis (for date & time)
 

Thanks Jon,

Worked like a charm!


"Jon Peltier" wrote in message
...
Try a different transformation. You want to adjust the fractional part so
that 0 is at 9 am and 1 is at 5:30 pm.

With your original date-time in column A, starting in A2, insert a column,
the new B column, and in B2 use this formula:

=INT(A2)+(A2-INT(A2)-TIME(9,0,0))/(TIME(17,30,0)-TIME(9,0,0))

and fill this down the column. Use column B as the new X value in your XY
chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Frank" wrote in message
...
Hi,

Im trying to plot some stock price data in a graph but running into the
following problem:

The data i want to plot is non continuous, i.e. the datetime should stop
at 5.30 pm and continu on 9 pm the next day. When i try to do that with a
scatter or line graph, it does not work.

I guess one workaround would be to make an additional series that
translates the date & time to e.g. 1, 2, 3 ,etc leaving out the dates&
times i dont want to show. Tried this and it seems to work except for the
fact that the interval between my datapoints isnt always the same. This
makes the scaling of the graph not correct. (eg time between 2 points is
sometimes 5 mins, and sometime only 1 minute)

any suggestions?