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?

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" <fasheldon@hotmail.com> wrote in message
news:u8SfR7O3GHA.3428@TK2MSFTNGP05.phx.gbl...
> 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?
>

Thanks Jon,

Worked like a charm!


"Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message
news:%23xLgKeX3GHA.2152@TK2MSFTNGP06.phx.gbl...
> 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" <fasheldon@hotmail.com> wrote in message
> news:u8SfR7O3GHA.3428@TK2MSFTNGP05.phx.gbl...
>> 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?
>>
>
>