Time-date chart plotting

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

0
Midjack (3)
3/7/2008 6:10:01 PM
excel.charting 18370 articles. 0 followers. Follow

13 Replies
804 Views

Similar Articles

[PageSpeed] 10

On Fri, 7 Mar 2008, in microsoft.public.excel.charting,
Midjack <Midjack@discussions.microsoft.com> 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 del@branta.demon.co.uk,
    which goes to a spam folder-- please send your email to del3 instead.
0
del1907 (586)
3/7/2008 10:40:16 PM
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 <Midjack@discussions.microsoft.com> 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 del@branta.demon.co.uk,
>     which goes to a spam folder-- please send your email to del3 instead.
> 
0
Midjack (3)
3/7/2008 11:08:01 PM
"Del Cotter" <del@branta.demon.co.uk> wrote in message 
news:QaOpz4AQRc0HFwlx@branta.demon.co.uk...
> On Fri, 7 Mar 2008, in microsoft.public.excel.charting,
> Midjack <Midjack@discussions.microsoft.com> 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
_______



0
jonxlmvpNO (4558)
3/8/2008 3:34:20 PM
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
0
David4882 (672)
6/25/2008 1:12:00 PM
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" <David@discussions.microsoft.com> wrote in message 
news:E750BDD8-045D-4809-BF1D-BA1A2E4361BB@microsoft.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 


0
jonxlmvpNO (4558)
6/25/2008 4:21:20 PM
On Wed, 25 Jun 2008, in microsoft.public.excel.charting,
Jon Peltier <jonxlmvpNO@SPAMpeltiertech.com> said:
>"David" <David@discussions.microsoft.com> 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 <jonxlmvpNO@SPAMpeltiertech.com> said:
>"Del Cotter" <del@branta.demon.co.uk> wrote in message
>> Midjack <Midjack@discussions.microsoft.com> 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 del@branta.demon.co.uk,
    which goes to a spam folder-- please send your email to del3 instead.
0
del1907 (586)
6/25/2008 5:56:05 PM
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" <del@branta.demon.co.uk> wrote in message 
news:I+Lvq1B1aoYIFwYn@branta.demon.co.uk...
> On Wed, 25 Jun 2008, in microsoft.public.excel.charting,
> Jon Peltier <jonxlmvpNO@SPAMpeltiertech.com> said:
>>"David" <David@discussions.microsoft.com> 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 <jonxlmvpNO@SPAMpeltiertech.com> said:
>>"Del Cotter" <del@branta.demon.co.uk> wrote in message
>>> Midjack <Midjack@discussions.microsoft.com> 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 
> del@branta.demon.co.uk,
>    which goes to a spam folder-- please send your email to del3 instead. 


0
jonxlmvpNO (4558)
6/25/2008 7:59:55 PM
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" <del@branta.demon.co.uk> wrote in message 
> news:I+Lvq1B1aoYIFwYn@branta.demon.co.uk...
> > On Wed, 25 Jun 2008, in microsoft.public.excel.charting,
> > Jon Peltier <jonxlmvpNO@SPAMpeltiertech.com> said:
> >>"David" <David@discussions.microsoft.com> 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 <jonxlmvpNO@SPAMpeltiertech.com> said:
> >>"Del Cotter" <del@branta.demon.co.uk> wrote in message
> >>> Midjack <Midjack@discussions.microsoft.com> 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 
> > del@branta.demon.co.uk,
> >    which goes to a spam folder-- please send your email to del3 instead. 
> 
> 
> 
0
David4882 (672)
6/26/2008 7:17:01 AM
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" <David@discussions.microsoft.com> wrote in message 
news:60AB6E64-B89C-49EC-9B21-5344E3400FBA@microsoft.com...
> 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" <del@branta.demon.co.uk> wrote in message
>> news:I+Lvq1B1aoYIFwYn@branta.demon.co.uk...
>> > On Wed, 25 Jun 2008, in microsoft.public.excel.charting,
>> > Jon Peltier <jonxlmvpNO@SPAMpeltiertech.com> said:
>> >>"David" <David@discussions.microsoft.com> 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 <jonxlmvpNO@SPAMpeltiertech.com> said:
>> >>"Del Cotter" <del@branta.demon.co.uk> wrote in message
>> >>> Midjack <Midjack@discussions.microsoft.com> 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
>> > del@branta.demon.co.uk,
>> >    which goes to a spam folder-- please send your email to del3 
>> > instead.
>>
>>
>> 


0
jonxlmvpNO (4558)
6/26/2008 4:50:20 PM
I've illustrated this in today's blog posting:

Line-XY Combination Charts
http://peltiertech.com/WordPress/2008/06/26/line-xy-combination-charts/

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


"Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message 
news:uX1cBz61IHA.1240@TK2MSFTNGP02.phx.gbl...
> 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" <David@discussions.microsoft.com> wrote in message 
> news:60AB6E64-B89C-49EC-9B21-5344E3400FBA@microsoft.com...
>> 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" <del@branta.demon.co.uk> wrote in message
>>> news:I+Lvq1B1aoYIFwYn@branta.demon.co.uk...
>>> > On Wed, 25 Jun 2008, in microsoft.public.excel.charting,
>>> > Jon Peltier <jonxlmvpNO@SPAMpeltiertech.com> said:
>>> >>"David" <David@discussions.microsoft.com> 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 <jonxlmvpNO@SPAMpeltiertech.com> said:
>>> >>"Del Cotter" <del@branta.demon.co.uk> wrote in message
>>> >>> Midjack <Midjack@discussions.microsoft.com> 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
>>> > del@branta.demon.co.uk,
>>> >    which goes to a spam folder-- please send your email to del3 
>>> > instead.
>>>
>>>
>>>
>
> 


0
jonxlmvpNO (4558)
6/26/2008 10:33:55 PM
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/2008/06/26/line-xy-combination-charts/
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
> 
> 
> "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message 
> news:uX1cBz61IHA.1240@TK2MSFTNGP02.phx.gbl...
> > 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" <David@discussions.microsoft.com> wrote in message 
> > news:60AB6E64-B89C-49EC-9B21-5344E3400FBA@microsoft.com...
> >> 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" <del@branta.demon.co.uk> wrote in message
> >>> news:I+Lvq1B1aoYIFwYn@branta.demon.co.uk...
> >>> > On Wed, 25 Jun 2008, in microsoft.public.excel.charting,
> >>> > Jon Peltier <jonxlmvpNO@SPAMpeltiertech.com> said:
> >>> >>"David" <David@discussions.microsoft.com> 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 <jonxlmvpNO@SPAMpeltiertech.com> said:
> >>> >>"Del Cotter" <del@branta.demon.co.uk> wrote in message
> >>> >>> Midjack <Midjack@discussions.microsoft.com> 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
> >>> > del@branta.demon.co.uk,
> >>> >    which goes to a spam folder-- please send your email to del3 
> >>> > instead.
> >>>
> >>>
> >>>
> >
> > 
> 
> 
> 
0
David4882 (672)
6/27/2008 7:33:00 AM
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
> 
0
Utf
4/10/2010 1:39:01 AM
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
>>
0
Jon
4/10/2010 4:27:55 PM
Reply:

Similar Artilces:

Charts, Help!
Can anyone tell me what I'm doing wrong? I'm trying to set the font size for the axis ticklabels to 6 for every chart in the active sheet. Sub SetFontSizeAllCharts() Dim myCht As ChartObject For Each myCht In ActiveSheet.ChartObjects myCht.Chart.TickLabels.Font.Size = 6 Next End Sub I get errors! Help! --- Message posted from http://www.ExcelForum.com/ Hi cdegar01, The problem is that you are not including the Axes object. Explanation extracted from the help file: expression.Axes(Type, AxisGroup) Type Optional Variant. Specifies the axis to return. Can be one of the follo...

How to make micro chart sizes?
Has anyone used mico charts? Johnk see my reply to your later post. Kelly "JOHNK" <JOHNK@discussions.microsoft.com> wrote in message news:277DE488-86AA-481D-9394-9DB00CA6CAFD@microsoft.com... > Has anyone used mico charts? ...

The x-axis title is truncated in EXCEL charts 2003.
The x axis title is truncated to at least 2 less letters than I added in the title not matter what the length of title. Ths did not happen in the previous EXCEL versions. How do you correct this? What version? Others have reported same trouble with Y axis title. Just add a few characters and format them to be invisible best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Stanley" <Stanley@discussions.microsoft.com> wrote in message news:2A6234B7-48A4-4C92-B83D-4DE90119DA04@microsoft.com... > The x axis title is truncated to at ...

date and bond calculations
Win XP HE, XL 2002 - follow-up: microsoft.public.excel.misc Hi, What is the best way to enter a maturity date to calculate PRICE function? If I am not looking at an actual bond with a stated maturity date but I want to run a theoretical one, using today's date as settlement (I can use function today() here) but maturity I wd like to say today + 10 years for example; instead of having to input the actual date 2015,4,29 for example, is there a way of using s'thing similar to today()+10 years or today + (360*10), etc. and how would one actually enter it in the formula (price for example)?...

Auto date changing in Excel is maddening
I'm using MS Office Excel 2003 (11.6113.5703) to post data that spans over several days. sample plot http://peswiki.com/index.php/Directory:Bedini_SG:Replications:PES:Sterling_Allan:Data:Exp10.4_Different_Standing_Discharge_Rates or try http://tinyurl.com/6f8zn We had to jump through all kinds of work-arounds in order to accomplish this. It was not a straight-forward process, though it should have been. When I enter the date format, for example, as Format > Time > 3/14/01 13:30 And then enter 11/17/04 0:30:30, it enters okay, but when I double click on the cell to edit it, Ex...

Linking sheets which must update the date first.
I have 50 clients, each with an Accounts Recievable worksheet. Past due is determined by using the =now and =today functions. This information is then linked to a Master sheet which displays all 50 clients at one time. The problem is: When I open the Master sheet, the dates on the 50 'feeder' sheets do not update the date. Only if those sheets are opened does the date update. Is there a command to open and close those 50 sheets or a recalculate command or perhaps, a macro is needed to open then close the 50 sheets. Any suggestions? I posted the same question abou...

Help with Chart of Accounts
We are using GP 7.50 and have two questions: If I want to add one new natural code for each of my facilities. What is the easiest way to do this??? I have 500 locations that I need to create this one code for...I am finding myself just creating one at a time because mass modify is not helpful. Does anyone have any ideas of some shortcuts??? The second question is we have two databases created in GP. One is the test version and one is our live. I want to copy the chart of accounts built in the test version to be copied to the live version. Can someone tell me how to do this so that...

Adding Text to Charts
I have created a chart in Excel that tracks one of the stock indexes. I have been able to point arrows to specific days and add text so I know what was the reason the stock index was up or down on that day. However, as my chart expands the arrows and text are no longer associated with those day I initially intended it for. Is there any way in Excel to associate text with specific day and have it attached so that if the chart grows or shrinks it will always be associated with that particular day. Thanks Hi Roy, The trick is to group the graphic element and the chart. When adding a graph...

Date formulas
I'm trying to conditional format some cells. I want to change the color of the font after a certain amount of days. The cell is a date I need it to change after it is >4 but <6 and to change again when it >6. If someone could helpit would I would be greatfull. Thank you, Rusty something like this format>conditional format>condition 1>formula is>=a4>a2+6 format>conditional format>condition 2>formula is>=a4>a2+4 -- Don Guillett SalesAid Software donaldb@281.com "Rusty" <rdailey@grandecom.net> wrote in message news:114iii5oge8hoa...

Charting data from multiple sheets
Is there a way to chart date from multiple sheets. I have used Qpro for years and want to get away form it but... I use one sheet per month of data and what to chart Jan to Dec for example. Any ideas? Sean - You need to summarize the data on a single sheet, then make your chart. You can use formulas to do this. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Sean Mc wrote: > Is there a way to chart date from multiple sheets. I have > used Qpro for years and want to get away form it but... I > use one sheet per mo...

Excel 2007 Stock market Charts
I teach a class in financial modeling. Some of my students use Excel 2003 and some 2007 (They are required to consult the company from whom they have accepted an offer and use the version of excel the company uses). In 2003 we generate a candlestick (Open High Low Close) Stock Chart for a company. We then add Volume. What is a good exercise in 2003 is a disaster in 2007. The candlestick has a fixed gap (unless you go into VBA (why would you change this?)). When you add volume to the chart it appears that the stock price movements take as the category labels the dates of the ...

date&time in signature
Can anybody tell me if it's possible to insert current date and time in a signature and have it updated whenever the signature is used. Thanks Chris ...

Floating Year-To-Date Line
Here's what I've got. I'm given a line graph charting dating on a weekly basis over a full year. A goal line has been adding by putting the goal in a second column, but it's the same number just repeated 60 times. Also, I've been asked to put in a floating YTD line. Each week when the data is updated, the YTD value will change. I want to add a line to the chart that graphically shows this value, but it has to be a line that goes across the whole graph and is only done with one data point. Does any of this make sense? Can anyone tell me how to do this? Hi, You can d...

Sorting search results by date created for multiple file types?
I am trying sort files that were created in a certain folder since December 5, 2009 until the current date. These files can be of the following file extensions PDF, ZIP, RAR. The files should be sorted in descending date created order with the oldest files on top. This works when one file type is involved. However, when the "Date Created" column is clicked, with multiple files in the search results; it does not sort correctly in descending or ascending order. How do I make Window XP sort the file search results in descending "Date Created" order with multiple...

Purchase Order "Title" and "Required Date" fields in main PO Scree
Purchase Order Selection Screen: Need the description (Title) of the PO to show up on this screen as well as the required date so we can distinguish between PO’s. It would also be handy to have the “Notes” field appear on this screen. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in th...

using the time structure with MFC
Hi, Coming from C, how could I translate this into MFC based code (using CString)? char szTime[65], szDate[65]; time_t t = time(0); struct tm* tm = localtime(&t); strftime(szDate, 64, "%Y%m%d", tm); strftime(szTime, 64, "%H%M%S", tm); CString strTime, strDate; Thanks CTime Time = CTime::GetCurrentTime(); CString strDate = Time.Format("%Y%m%d"); CString strTime = Time.Format("%H%M%S"); AliR. "markww" <markww@gmail.com> wrote in message news:1156883820.948360.39250@m79g200...

Resource Scheduling and Time Zone
Currently have an East coast office with an Exchange 2003 Front-end and a back-end server. On the West 1 Exchange 2003 backend server. All the Resources have been setup and the appropriate time zone select during the setup process. The problem we are having is when someone schedules a meeting and selects resources (rooms) for both locations the system doesn’t re-calculate the difference in time zones. So a east coast meeting time of 10:00 am EST shows in the west as 10:00am PST. Is there a fix or work around? ...

Excel 2000 Header Date Format..
I'm moving from MS Works to MS Office 2000. In Excel, the date in the Header is all numbers, for example, today's date is 29-01-09 but I want it to show 29 January 2009. I can't figure out what to change to get the proper date format. I do want the format as dd-mm-yyyy but I want the month to be January not 01. I'm using Windows XP home edition SP3. -- JD.. Excel takes the header date from your Windows short date settings. You cannot change that setting to the format you want to see. You can run a macro to set the date as you wish. Sub header_date() ActiveSheet.Pag...

Conditions on date fiedls for workflow rules
I need to create a workflow rule that accomplishes the following: I need to create appointments for a patient, in a way that I can trigger a rule that consider the date for that appointment, and trigger the posting of a remind letter 2 months before and the creation of a remind call activity 7 days before. Since I cannot use conditions on activities within the Workflow Manager, I was thinking of treating the appointments as Opportunities and create an Opportunity rule, but my surprise is that I still cannot use conditions on date fields excepts on "createdon". Has anybody ever had...

How to delete chart
I created a histogram and want to delete it. I select it and the faint double border apperars. There are no square handles like in 2003. Then I press the Delete key. The faint double border goes away replaced by a single thin black border. The chart is still there and is not able to be selected at all. I entered "delete chart" in the Excel Help window, and there was nothing at all on deleting a chart. Ditto for "remove chart." I got no help searching on the internet either. I have Excel 2007 and Windows XP Professional When the border changes, can you press Delete a...

Creating a chart
Hi there, I need to create a chart, line graph, for the following table. First row contains date: 30/09/2007 01/10/2007 02/10/2007 03/10/2007 04/10/2007 05/10/2007 Second row contains : DB DG DG DR DG YL Third row contains: R R R G G B I've tried converting them in columns, but the graph does not come correct. Please, help it is urgent.... Thanks. Kind regards, Rashi ...

Skipping every other data element when charting
When I am charting it is skipping every other data element/row. So for example if I have sally with 40% and then Joe with 70% and then Mary with 80% it only lists Sally and Mary on the chart but shows the bar for Joe, just doesn't list his name under the bar. Why is that? Hi, Double click the category axis and on the scale tab make sure the setting of 'Number of Categories between tick mark labels' is 1. Cheers Andy flegendre wrote: > When I am charting it is skipping every other data element/row. So for > example if I have sally with 40% and then Joe with 70% and th...

Chart with real time on the X axis and Variable Size
Greetings, I need to chart an imported data point or a calculated result in a cell, based on 'real time" (timestamp) on the X-Axis. The data set will have to grow automaticaly as the time increases for several hundred time-stamps. Thank you for your imput. Make a dynamic XY chart (line charts can represent dates well, but not times). There are dynamic charting examples and links here: http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ SP...

Chart toolbar popping up within internet explorer
When I open an excel chart within Internet Explorer, I get Excel's annoying Chart toolbar right in the middle of the chart! My users have to click the "X" to make it go away before viewing the chart. I know i can run a macro at opening to not display the toolbar, but then that forces them to click "Enable macros" which may be even more annoying. Anybody else run into this issue before when opening an Excel file (or any other Office file) within IE? thanks for any help! ...

Economic Charts
Hi, Does anyone know if I can use Excel to calculate economic charts such as Production Possibility Frontiers and Comparative Advantage charts. If so could you please let me know how. Many Thanks A Frustrated Student Each chart needs some X values and Y values, no matter how fancy the name of the chart. If you have some input data, and a relationship between these and the values being plotted, put the inputs into various columns. In the next two columns, put worksheet formulas into the cells to calculate X and Y from these inputs. Then select these two columns of data, start the chart...