Discretionary X-axis (for date & time)

[Frank]

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?

 

[Jon Peltier]

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

 

[Frank]

Thanks Jon,

Worked like a charm!

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
_______