How can I create a time-date graph such that the values are plotted at the
correct time positions within the date range rather than all on the date
vertical axis?

N N
N N
N N
N N THIS
N N
N N
Jan 1 Jan 2

Rather than
N N
N N
N N NOT THIS
N N
N N
N N
Jan 1 Jan 2

On Fri, 7 Mar 2008, in microsoft.public.excel.charting,
Midjack said:
How can I create a time-date graph such that the values are plotted at the
correct time positions within the date range rather than all on the date
vertical axis?

Choose Chart type XY (Scatter) instead of Line. Line has only the
options of Category (which won't distribute the values properly) and
Time-scale (which, for some bizarre reason, Microsoft limits to whole
days only - no times available).

Time-scale can sometimes be useful as a sort of Interval scale for Line
and Area chart types, but because it only does integers, not fractions,
it's no good to you here. Only a proper interval/interval chart type
will do.

Also, make sure the X values are proper "date plus time" format all in
one cell. A date in one cell and a time in another won't work.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Many thanks Del. That seems to work fine after a little tinkering with both
of the axes.

"Del Cotter" wrote:

On Fri, 7 Mar 2008, in microsoft.public.excel.charting,
Midjack said:
How can I create a time-date graph such that the values are plotted at the
correct time positions within the date range rather than all on the date
vertical axis?

Choose Chart type XY (Scatter) instead of Line. Line has only the
options of Category (which won't distribute the values properly) and
Time-scale (which, for some bizarre reason, Microsoft limits to whole
days only - no times available).

Time-scale can sometimes be useful as a sort of Interval scale for Line
and Area chart types, but because it only does integers, not fractions,
it's no good to you here. Only a proper interval/interval chart type
will do.

Also, make sure the X values are proper "date plus time" format all in
one cell. A date in one cell and a time in another won't work.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

"Del Cotter" wrote in message
...
On Fri, 7 Mar 2008, in microsoft.public.excel.charting,
Midjack said:
How can I create a time-date graph such that the values are plotted at the
correct time positions within the date range rather than all on the date
vertical axis?

Choose Chart type XY (Scatter) instead of Line. Line has only the options
of Category (which won't distribute the values properly) and Time-scale
(which, for some bizarre reason, Microsoft limits to whole days only - no
times available).

Del -

It's still a category axis. I think the magic done under the covers simply
skips some categories in between to plot uneven intervals of whole numbers.
So we're still stuck with days only.

What you could do if you like the way the date axis is formatted (I know I
do), is to make a line chart with a hidden series (no markers or lines) to
control the axis, then add your real data as XY series, using real dates and
times for the X values. These somehow are allowed to display in between
categories and show times other than midnight on the days along the axis.
This combination Line-XY approach also helps to display data where the
series don't have points on the same days. Line chart series are tied to the
dates for the first series, but XY series are independent of each other.

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

Jon,
This is just what I need. I've had a go, but am failing miserably so far.
I've been looking at your pages for an example but no joy so far. If there is
a specific example on your site could you please point me at it.
Thanks

Too bad you deleted the post you were replying to.

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


"David" wrote in message
...
Jon,
This is just what I need. I've had a go, but am failing miserably so far.
I've been looking at your pages for an example but no joy so far. If there
is
a specific example on your site could you please point me at it.
Thanks

On Wed, 25 Jun 2008, in microsoft.public.excel.charting,
Jon Peltier said:
"David" wrote
This is just what I need. I've had a go, but am failing miserably so
far. I've been looking at your pages for an example but no joy so far.
If there is a specific example on your site could you please point me
at it.

Too bad you deleted the post you were replying to.

Jon, this is the context:

On Sat, 8 Mar 2008, in microsoft.public.excel.charting,
Jon Peltier said:
"Del Cotter" wrote in message
Midjack said:
How can I create a time-date graph such that the values are plotted at the
correct time positions within the date range rather than all on the date
vertical axis?

Choose Chart type XY (Scatter) instead of Line. Line has only the options
of Category (which won't distribute the values properly) and Time-scale

What you could do if you like the way the date axis is formatted (I know I
do), is to make a line chart with a hidden series (no markers or lines) to
control the axis, then add your real data as XY series, using real dates and
times for the X values. These somehow are allowed to display in between
categories and show times other than midnight on the days along the axis.
This combination Line-XY approach also helps to display data where the
series don't have points on the same days. Line chart series are tied to the
dates for the first series, but XY series are independent of each other.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

I figured it was something like that. Too lazy to look it up.

Here's the protocol, which doesn't work in 2007 (doh! MS broke the
independence of the XY and line series in a combo chart with a date scale
axis).

Make a series with two points, like the min and max of your X axis
(date-time), and zero y values. Format the axis as you like it, and format
the series to be invisible (no line, no markers). Copy the XY data for the
XY series, select the chart, use Paste Special to add the data as a new
series. Right click this series, Chart Type, choose XY. Right click the
series (now XY), Format, and move it back to the primary axis. Repeat to add
more XY series if necessary.

I have an upcoming series for my blog which will deal with category axes,
but I need more time to make it pretty.

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


"Del Cotter" wrote in message
...
On Wed, 25 Jun 2008, in microsoft.public.excel.charting,
Jon Peltier said:
"David" wrote
This is just what I need. I've had a go, but am failing miserably so far.
I've been looking at your pages for an example but no joy so far. If
there is a specific example on your site could you please point me at it.

Too bad you deleted the post you were replying to.

Jon, this is the context:

On Sat, 8 Mar 2008, in microsoft.public.excel.charting,
Jon Peltier said:
"Del Cotter" wrote in message
Midjack said:
How can I create a time-date graph such that the values are plotted at
the
correct time positions within the date range rather than all on the date
vertical axis?

Choose Chart type XY (Scatter) instead of Line. Line has only the
options
of Category (which won't distribute the values properly) and Time-scale

What you could do if you like the way the date axis is formatted (I know I
do), is to make a line chart with a hidden series (no markers or lines) to
control the axis, then add your real data as XY series, using real dates
and
times for the X values. These somehow are allowed to display in between
categories and show times other than midnight on the days along the axis.
This combination Line-XY approach also helps to display data where the
series don't have points on the same days. Line chart series are tied to
the
dates for the first series, but XY series are independent of each other.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

Thanks for that Jon (and sorry about deleting post last time)
I am using Excel 2002
I followed your protocol and produced a chart but the xy data did not quite
line up with the category x axis like i was hoping.
Here's my numbers:
Line chart: (01/01/01, 0) (03/01/01, 0)
set xmin = 01/01/01, set xmax = 04/01/01
xy data: (01/01/01 05:00,1) (03/01/01 17:00, 4)
after copy, paste special, chart type = xy, format\axis\primary axis
I find that the 2nd xy data point is out of position in the 04/01/01
interval on the x category axis (rather than 17/24ths along the 03/01/01
interval as hoped for)
Is this in line with your expectation?
Thanks


"Jon Peltier" wrote:

I figured it was something like that. Too lazy to look it up.

Here's the protocol, which doesn't work in 2007 (doh! MS broke the
independence of the XY and line series in a combo chart with a date scale
axis).

Make a series with two points, like the min and max of your X axis
(date-time), and zero y values. Format the axis as you like it, and format
the series to be invisible (no line, no markers). Copy the XY data for the
XY series, select the chart, use Paste Special to add the data as a new
series. Right click this series, Chart Type, choose XY. Right click the
series (now XY), Format, and move it back to the primary axis. Repeat to add
more XY series if necessary.

I have an upcoming series for my blog which will deal with category axes,
but I need more time to make it pretty.

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


"Del Cotter" wrote in message
...
On Wed, 25 Jun 2008, in microsoft.public.excel.charting,
Jon Peltier said:
"David" wrote
This is just what I need. I've had a go, but am failing miserably so far.
I've been looking at your pages for an example but no joy so far. If
there is a specific example on your site could you please point me at it.

Too bad you deleted the post you were replying to.

Jon, this is the context:

On Sat, 8 Mar 2008, in microsoft.public.excel.charting,
Jon Peltier said:
"Del Cotter" wrote in message
Midjack said:
How can I create a time-date graph such that the values are plotted at
the
correct time positions within the date range rather than all on the date
vertical axis?

Choose Chart type XY (Scatter) instead of Line. Line has only the
options
of Category (which won't distribute the values properly) and Time-scale

What you could do if you like the way the date axis is formatted (I know I
do), is to make a line chart with a hidden series (no markers or lines) to
control the axis, then add your real data as XY series, using real dates
and
times for the X values. These somehow are allowed to display in between
categories and show times other than midnight on the days along the axis.
This combination Line-XY approach also helps to display data where the
series don't have points on the same days. Line chart series are tied to
the
dates for the first series, but XY series are independent of each other.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

You have to format the date scale axis so that the value (Y) axis does NOT
cross between dates. Otherwise the chart is still correct, but the ticks
occur at noon, not at midnight, so the XY points appear incorrect.

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


"David" wrote in message
...
Thanks for that Jon (and sorry about deleting post last time)
I am using Excel 2002
I followed your protocol and produced a chart but the xy data did not
quite
line up with the category x axis like i was hoping.
Here's my numbers:
Line chart: (01/01/01, 0) (03/01/01, 0)
set xmin = 01/01/01, set xmax = 04/01/01
xy data: (01/01/01 05:00,1) (03/01/01 17:00, 4)
after copy, paste special, chart type = xy, format\axis\primary axis
I find that the 2nd xy data point is out of position in the 04/01/01
interval on the x category axis (rather than 17/24ths along the 03/01/01
interval as hoped for)
Is this in line with your expectation?
Thanks


"Jon Peltier" wrote:

I figured it was something like that. Too lazy to look it up.

Here's the protocol, which doesn't work in 2007 (doh! MS broke the
independence of the XY and line series in a combo chart with a date scale
axis).

Make a series with two points, like the min and max of your X axis
(date-time), and zero y values. Format the axis as you like it, and
format
the series to be invisible (no line, no markers). Copy the XY data for
the
XY series, select the chart, use Paste Special to add the data as a new
series. Right click this series, Chart Type, choose XY. Right click the
series (now XY), Format, and move it back to the primary axis. Repeat to
add
more XY series if necessary.

I have an upcoming series for my blog which will deal with category axes,
but I need more time to make it pretty.

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


"Del Cotter" wrote in message
...
On Wed, 25 Jun 2008, in microsoft.public.excel.charting,
Jon Peltier said:
"David" wrote
This is just what I need. I've had a go, but am failing miserably so
far.
I've been looking at your pages for an example but no joy so far. If
there is a specific example on your site could you please point me at
it.

Too bad you deleted the post you were replying to.

Jon, this is the context:

On Sat, 8 Mar 2008, in microsoft.public.excel.charting,
Jon Peltier said:
"Del Cotter" wrote in message
Midjack said:
How can I create a time-date graph such that the values are plotted
at
the
correct time positions within the date range rather than all on the
date
vertical axis?

Choose Chart type XY (Scatter) instead of Line. Line has only the
options
of Category (which won't distribute the values properly) and
Time-scale

What you could do if you like the way the date axis is formatted (I
know I
do), is to make a line chart with a hidden series (no markers or lines)
to
control the axis, then add your real data as XY series, using real
dates
and
times for the X values. These somehow are allowed to display in between
categories and show times other than midnight on the days along the
axis.
This combination Line-XY approach also helps to display data where the
series don't have points on the same days. Line chart series are tied
to
the
dates for the first series, but XY series are independent of each
other.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3
instead.

I've illustrated this in today's blog posting:

Line-XY Combination Charts
http://peltiertech.com/WordPress/200...nation-charts/

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


"Jon Peltier" wrote in message
...
You have to format the date scale axis so that the value (Y) axis does NOT
cross between dates. Otherwise the chart is still correct, but the ticks
occur at noon, not at midnight, so the XY points appear incorrect.

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


"David" wrote in message
...
Thanks for that Jon (and sorry about deleting post last time)
I am using Excel 2002
I followed your protocol and produced a chart but the xy data did not
quite
line up with the category x axis like i was hoping.
Here's my numbers:
Line chart: (01/01/01, 0) (03/01/01, 0)
set xmin = 01/01/01, set xmax = 04/01/01
xy data: (01/01/01 05:00,1) (03/01/01 17:00, 4)
after copy, paste special, chart type = xy, format\axis\primary axis
I find that the 2nd xy data point is out of position in the 04/01/01
interval on the x category axis (rather than 17/24ths along the 03/01/01
interval as hoped for)
Is this in line with your expectation?
Thanks


"Jon Peltier" wrote:

I figured it was something like that. Too lazy to look it up.

Here's the protocol, which doesn't work in 2007 (doh! MS broke the
independence of the XY and line series in a combo chart with a date
scale
axis).

Make a series with two points, like the min and max of your X axis
(date-time), and zero y values. Format the axis as you like it, and
format
the series to be invisible (no line, no markers). Copy the XY data for
the
XY series, select the chart, use Paste Special to add the data as a new
series. Right click this series, Chart Type, choose XY. Right click the
series (now XY), Format, and move it back to the primary axis. Repeat to
add
more XY series if necessary.

I have an upcoming series for my blog which will deal with category
axes,
but I need more time to make it pretty.

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


"Del Cotter" wrote in message
...
On Wed, 25 Jun 2008, in microsoft.public.excel.charting,
Jon Peltier said:
"David" wrote
This is just what I need. I've had a go, but am failing miserably so
far.
I've been looking at your pages for an example but no joy so far. If
there is a specific example on your site could you please point me at
it.

Too bad you deleted the post you were replying to.

Jon, this is the context:

On Sat, 8 Mar 2008, in microsoft.public.excel.charting,
Jon Peltier said:
"Del Cotter" wrote in message
Midjack said:
How can I create a time-date graph such that the values are plotted
at
the
correct time positions within the date range rather than all on the
date
vertical axis?

Choose Chart type XY (Scatter) instead of Line. Line has only the
options
of Category (which won't distribute the values properly) and
Time-scale

What you could do if you like the way the date axis is formatted (I
know I
do), is to make a line chart with a hidden series (no markers or
lines) to
control the axis, then add your real data as XY series, using real
dates
and
times for the X values. These somehow are allowed to display in
between
categories and show times other than midnight on the days along the
axis.
This combination Line-XY approach also helps to display data where the
series don't have points on the same days. Line chart series are tied
to
the
dates for the first series, but XY series are independent of each
other.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3
instead.

Thanks Jon,
I've read the blog and find it to be informative and well presented


"Jon Peltier" wrote:

I've illustrated this in today's blog posting:

Line-XY Combination Charts
http://peltiertech.com/WordPress/200...nation-charts/

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


"Jon Peltier" wrote in message
...
You have to format the date scale axis so that the value (Y) axis does NOT
cross between dates. Otherwise the chart is still correct, but the ticks
occur at noon, not at midnight, so the XY points appear incorrect.

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


"David" wrote in message
...
Thanks for that Jon (and sorry about deleting post last time)
I am using Excel 2002
I followed your protocol and produced a chart but the xy data did not
quite
line up with the category x axis like i was hoping.
Here's my numbers:
Line chart: (01/01/01, 0) (03/01/01, 0)
set xmin = 01/01/01, set xmax = 04/01/01
xy data: (01/01/01 05:00,1) (03/01/01 17:00, 4)
after copy, paste special, chart type = xy, format\axis\primary axis
I find that the 2nd xy data point is out of position in the 04/01/01
interval on the x category axis (rather than 17/24ths along the 03/01/01
interval as hoped for)
Is this in line with your expectation?
Thanks


"Jon Peltier" wrote:

I figured it was something like that. Too lazy to look it up.

Here's the protocol, which doesn't work in 2007 (doh! MS broke the
independence of the XY and line series in a combo chart with a date
scale
axis).

Make a series with two points, like the min and max of your X axis
(date-time), and zero y values. Format the axis as you like it, and
format
the series to be invisible (no line, no markers). Copy the XY data for
the
XY series, select the chart, use Paste Special to add the data as a new
series. Right click this series, Chart Type, choose XY. Right click the
series (now XY), Format, and move it back to the primary axis. Repeat to
add
more XY series if necessary.

I have an upcoming series for my blog which will deal with category
axes,
but I need more time to make it pretty.

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


"Del Cotter" wrote in message
...
On Wed, 25 Jun 2008, in microsoft.public.excel.charting,
Jon Peltier said:
"David" wrote
This is just what I need. I've had a go, but am failing miserably so
far.
I've been looking at your pages for an example but no joy so far. If
there is a specific example on your site could you please point me at
it.

Too bad you deleted the post you were replying to.

Jon, this is the context:

On Sat, 8 Mar 2008, in microsoft.public.excel.charting,
Jon Peltier said:
"Del Cotter" wrote in message
Midjack said:
How can I create a time-date graph such that the values are plotted
at
the
correct time positions within the date range rather than all on the
date
vertical axis?

Choose Chart type XY (Scatter) instead of Line. Line has only the
options
of Category (which won't distribute the values properly) and
Time-scale

What you could do if you like the way the date axis is formatted (I
know I
do), is to make a line chart with a hidden series (no markers or
lines) to
control the axis, then add your real data as XY series, using real
dates
and
times for the X values. These somehow are allowed to display in
between
categories and show times other than midnight on the days along the
axis.
This combination Line-XY approach also helps to display data where the
series don't have points on the same days. Line chart series are tied
to
the
dates for the first series, but XY series are independent of each
other.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3
instead.

You can try changing the axis to text format

"Midjack" wrote:

How can I create a time-date graph such that the values are plotted at the
correct time positions within the date range rather than all on the date
vertical axis?

N N
N N
N N
N N THIS
N N
N N
Jan 1 Jan 2

Rather than
N N
N N
N N NOT THIS
N N
N N
N N
Jan 1 Jan 2

Even better, use an XY chart. You need the full date and time values in
the cells containing the X values.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/9/2010 9:39 PM, water wrote:
You can try changing the axis to text format

"Midjack" wrote:

How can I create a time-date graph such that the values are plotted at the
correct time positions within the date range rather than all on the date
vertical axis?

N N
N N
N N
N N THIS
N N
N N
Jan 1 Jan 2

Rather than
N N
N N
N N NOT THIS
N N
N N
N N
Jan 1 Jan 2