How chart time scale x-axis with another date serie

Hello all. I have this problem, I will need to create this chart that I don't 
know how to. 

Serie 1 is below which needs to be time scaled
Date                Count
1/1/2006             12
1/2/2006             3
1/3/2006             5
2/1/2006             18
2/3/2006             4
2/18/2006           9
4/1/2006             18
7/4/2006             38

Serie 2 - needs to plot the three dates in serie 1's time scaled X-Axis with 
symbols.

1/31/2006
2/18/2006
4/2/2006

I will look something like you have a line of count on a time-scaled chart, 
with 3 dates point in the x-axis.  Please give me some advice on how to do 
it. Thank you all. 



0
luvgreen (16)
11/10/2006 8:44:01 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
1182 Views

Similar Articles

[PageSpeed] 53

Make the chart with the first series as a line chart with a time scale axis.

Add the data for the second series. Convert this series to an XY series 
(select the series, Chart menu > Chart Type). Put the XY series onto the 
primary axis (double click, Axis tab).

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


"luvgreen" <luvgreen@discussions.microsoft.com> wrote in message 
news:6452C196-F731-417E-9626-C4F3159B9D1F@microsoft.com...
> Hello all. I have this problem, I will need to create this chart that I 
> don't
> know how to.
>
> Serie 1 is below which needs to be time scaled
> Date                Count
> 1/1/2006             12
> 1/2/2006             3
> 1/3/2006             5
> 2/1/2006             18
> 2/3/2006             4
> 2/18/2006           9
> 4/1/2006             18
> 7/4/2006             38
>
> Serie 2 - needs to plot the three dates in serie 1's time scaled X-Axis 
> with
> symbols.
>
> 1/31/2006
> 2/18/2006
> 4/2/2006
>
> I will look something like you have a line of count on a time-scaled 
> chart,
> with 3 dates point in the x-axis.  Please give me some advice on how to do
> it. Thank you all.
>
>
> 


0
jonxlmvpNO (4558)
11/10/2006 9:02:41 PM
Jon:

Thank you s much for your reply. You answered my last time's similar 
question too. :)

Is it possible to generate this kind of chart from VBA code? Thanks again. 

"Jon Peltier" wrote:

> Make the chart with the first series as a line chart with a time scale axis.
> 
> Add the data for the second series. Convert this series to an XY series 
> (select the series, Chart menu > Chart Type). Put the XY series onto the 
> primary axis (double click, Axis tab).
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
> 
> 
> "luvgreen" <luvgreen@discussions.microsoft.com> wrote in message 
> news:6452C196-F731-417E-9626-C4F3159B9D1F@microsoft.com...
> > Hello all. I have this problem, I will need to create this chart that I 
> > don't
> > know how to.
> >
> > Serie 1 is below which needs to be time scaled
> > Date                Count
> > 1/1/2006             12
> > 1/2/2006             3
> > 1/3/2006             5
> > 2/1/2006             18
> > 2/3/2006             4
> > 2/18/2006           9
> > 4/1/2006             18
> > 7/4/2006             38
> >
> > Serie 2 - needs to plot the three dates in serie 1's time scaled X-Axis 
> > with
> > symbols.
> >
> > 1/31/2006
> > 2/18/2006
> > 4/2/2006
> >
> > I will look something like you have a line of count on a time-scaled 
> > chart,
> > with 3 dates point in the x-axis.  Please give me some advice on how to do
> > it. Thank you all.
> >
> >
> > 
> 
> 
> 
0
luvgreen (16)
11/10/2006 9:15:01 PM
If you can do it manually, you can do it in VBA.

I have a bunch of examples and hints on programming Excel charts:

    http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

Use the macro recorder (one of my two favorite programming tools, Google's 
the other) to get some rough code, and use the page above to refine it. 
Basically, record a macro while making a line chart and then changing one 
series to an XY style.

The macro first looks something like this:

Sub Macro1()
' Macro recorded 11/10/2006 by Jon Peltier
    Range("B6:C13").Select
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B6:C13")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    ActiveWindow.Visible = False
    Windows("Book3").Activate
    Range("E6:F11").Select
    Selection.Copy
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, 
SeriesLabels:=True, _
        CategoryLabels:=True, Replace:=False, NewSeries:=True
    ActiveChart.SeriesCollection(2).Select
    Application.CutCopyMode = False
    ActiveChart.SeriesCollection(2).ChartType = xlXYScatterLines
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).AxisGroup = 1
End Sub

Fix it up a little:

Sub Macro1()
' Macro fixed up 11/10/2006 by Jon Peltier
    Dim cht As Chart
    Dim wks As Worksheet
    Dim srs As Series

    Set wks = ActiveSheet
    Set cht = wks.ChartObjects.Add(250, 150, 450, 250).Chart
    With cht
        .SetSourceData Source:=wks.Range("B6:C13")
        .ChartType = xlLineMarkers
        Set srs = .SeriesCollection.NewSeries
        With srs
            .Values = wks.Range("F7:F11")
            .XValues = wks.Range("E7:E11")
            .Name = wks.Range("F6")
            .ChartType = xlXYScatterLines
            .AxisGroup = 1
        End With
    End With
End Sub

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


"luvgreen" <luvgreen@discussions.microsoft.com> wrote in message 
news:6CC1F590-F689-4BFC-8992-80241F7398EE@microsoft.com...
> Jon:
>
> Thank you s much for your reply. You answered my last time's similar
> question too. :)
>
> Is it possible to generate this kind of chart from VBA code? Thanks again.
>
> "Jon Peltier" wrote:
>
>> Make the chart with the first series as a line chart with a time scale 
>> axis.
>>
>> Add the data for the second series. Convert this series to an XY series
>> (select the series, Chart menu > Chart Type). Put the XY series onto the
>> primary axis (double click, Axis tab).
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>>
>> "luvgreen" <luvgreen@discussions.microsoft.com> wrote in message
>> news:6452C196-F731-417E-9626-C4F3159B9D1F@microsoft.com...
>> > Hello all. I have this problem, I will need to create this chart that I
>> > don't
>> > know how to.
>> >
>> > Serie 1 is below which needs to be time scaled
>> > Date                Count
>> > 1/1/2006             12
>> > 1/2/2006             3
>> > 1/3/2006             5
>> > 2/1/2006             18
>> > 2/3/2006             4
>> > 2/18/2006           9
>> > 4/1/2006             18
>> > 7/4/2006             38
>> >
>> > Serie 2 - needs to plot the three dates in serie 1's time scaled X-Axis
>> > with
>> > symbols.
>> >
>> > 1/31/2006
>> > 2/18/2006
>> > 4/2/2006
>> >
>> > I will look something like you have a line of count on a time-scaled
>> > chart,
>> > with 3 dates point in the x-axis.  Please give me some advice on how to 
>> > do
>> > it. Thank you all.
>> >
>> >
>> >
>>
>>
>> 


0
jonxlmvpNO (4558)
11/11/2006 2:44:21 AM
Thank you so very much Jon. I do appreciate your kindness to help. 



"Jon Peltier" wrote:

> If you can do it manually, you can do it in VBA.
> 
> I have a bunch of examples and hints on programming Excel charts:
> 
>     http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html
> 
> Use the macro recorder (one of my two favorite programming tools, Google's 
> the other) to get some rough code, and use the page above to refine it. 
> Basically, record a macro while making a line chart and then changing one 
> series to an XY style.
> 
> The macro first looks something like this:
> 
> Sub Macro1()
> ' Macro recorded 11/10/2006 by Jon Peltier
>     Range("B6:C13").Select
>     Charts.Add
>     ActiveChart.ChartType = xlLineMarkers
>     ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B6:C13")
>     ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
>     ActiveWindow.Visible = False
>     Windows("Book3").Activate
>     Range("E6:F11").Select
>     Selection.Copy
>     ActiveSheet.ChartObjects("Chart 1").Activate
>     ActiveChart.ChartArea.Select
>     ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, 
> SeriesLabels:=True, _
>         CategoryLabels:=True, Replace:=False, NewSeries:=True
>     ActiveChart.SeriesCollection(2).Select
>     Application.CutCopyMode = False
>     ActiveChart.SeriesCollection(2).ChartType = xlXYScatterLines
>     ActiveChart.SeriesCollection(2).Select
>     ActiveChart.SeriesCollection(2).AxisGroup = 1
> End Sub
> 
> Fix it up a little:
> 
> Sub Macro1()
> ' Macro fixed up 11/10/2006 by Jon Peltier
>     Dim cht As Chart
>     Dim wks As Worksheet
>     Dim srs As Series
> 
>     Set wks = ActiveSheet
>     Set cht = wks.ChartObjects.Add(250, 150, 450, 250).Chart
>     With cht
>         .SetSourceData Source:=wks.Range("B6:C13")
>         .ChartType = xlLineMarkers
>         Set srs = .SeriesCollection.NewSeries
>         With srs
>             .Values = wks.Range("F7:F11")
>             .XValues = wks.Range("E7:E11")
>             .Name = wks.Range("F6")
>             .ChartType = xlXYScatterLines
>             .AxisGroup = 1
>         End With
>     End With
> End Sub
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
> 
> 
> "luvgreen" <luvgreen@discussions.microsoft.com> wrote in message 
> news:6CC1F590-F689-4BFC-8992-80241F7398EE@microsoft.com...
> > Jon:
> >
> > Thank you s much for your reply. You answered my last time's similar
> > question too. :)
> >
> > Is it possible to generate this kind of chart from VBA code? Thanks again.
> >
> > "Jon Peltier" wrote:
> >
> >> Make the chart with the first series as a line chart with a time scale 
> >> axis.
> >>
> >> Add the data for the second series. Convert this series to an XY series
> >> (select the series, Chart menu > Chart Type). Put the XY series onto the
> >> primary axis (double click, Axis tab).
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Tutorials and Custom Solutions
> >> http://PeltierTech.com
> >> _______
> >>
> >>
> >> "luvgreen" <luvgreen@discussions.microsoft.com> wrote in message
> >> news:6452C196-F731-417E-9626-C4F3159B9D1F@microsoft.com...
> >> > Hello all. I have this problem, I will need to create this chart that I
> >> > don't
> >> > know how to.
> >> >
> >> > Serie 1 is below which needs to be time scaled
> >> > Date                Count
> >> > 1/1/2006             12
> >> > 1/2/2006             3
> >> > 1/3/2006             5
> >> > 2/1/2006             18
> >> > 2/3/2006             4
> >> > 2/18/2006           9
> >> > 4/1/2006             18
> >> > 7/4/2006             38
> >> >
> >> > Serie 2 - needs to plot the three dates in serie 1's time scaled X-Axis
> >> > with
> >> > symbols.
> >> >
> >> > 1/31/2006
> >> > 2/18/2006
> >> > 4/2/2006
> >> >
> >> > I will look something like you have a line of count on a time-scaled
> >> > chart,
> >> > with 3 dates point in the x-axis.  Please give me some advice on how to 
> >> > do
> >> > it. Thank you all.
> >> >
> >> >
> >> >
> >>
> >>
> >> 
> 
> 
> 
0
luvgreen (16)
11/13/2006 2:32:02 PM
Reply:

Similar Artilces:

How to select series in chart?
I know I asked this question before, but (sigh) I cannot find the answer now, when I need it of course. How can I select a series in an Excel chart (XY Scatter) using the keyboard, not the mouse? The issue is: I have overlapping series, so it is difficult for me to select a series by moving the mouse cursor to a point in one series and right-clicking it, as I normally do. Someone once mentioned a ctrl and/or shift key combination (I think) that would allow me to select each series explicit in round-robin fashion. That is what I am looking for again. More generally, how could I have found...

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Linking a Chart with graphics to ppt
Dear group, when I create a chart with scatters, which carry graphics like Excel-Block-Arrows and link this to Powerpoint, the graphics are shown there in a very low quality with large pixels. Any ideas how to improve this? Thanks in advance, Holger. ...

Timesheet to monitor flexi-time
Hi All, Does anyone have a spreadsheet that can be used to record employee timesheet details, capable of accommodating simple flexi-time and part time hours. Ideally, i would like an option to add employees and then enter their agreed working hours (this is to be used as a look up). Then, On a weekly basis, data will be entered for the previous 7 days� Hours worked can fall under the following categories... Hours worked �normal� time Hours worked flexi time Hours �Sick� Holiday Bank Holiday Authorised Absence Paid Authorised absence unpaid I'd also like A way of viewing information...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

Multiple stacked bar charts
I have a project where i have to chart the compliance of three groups over a number of months. So for each data set there is a Compliance#1 stacked upon NonCompliance#1, then Compliance#2 stacked upon NonCompliance#2 and finally, you guessed it, Compliance#3 stacked upon NonCompliance#3. When i design a chart in excel it wants to place everything on top of each other producing one column of six not the required three columns of two for each week in the display. Can anyone point me i the right direction for this one?? Hi you can achieve this effect by having two layers in your x axis the at...

transparent areas in charts?
How do you create transparent areas in imbedded charts? (for Excel 2003). When I click on "Format Chart area", then "Fill effects", there is a place where you can change the transparency settings, but it is faded out and will not work. Any Ideas? You can make most elements totally transparent. Ignore the transparency button, which is only for decoration. Instead, if you set an element's area to none, the element will be transparent. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech....

printing 2 charts on single page
Using Excel 2k, Win 2k I was trying to print to line charts on a single page to demonstrate the corelation between the two data sets. I would like to print in portait mode with one chart on top of page and one on bottom. Excel only seems to offer putting chart 2 on a separate page or embedding it in chart one. I am not very familiar with excel charting capabilites can anyone suggest a resorce? Thanks ...

Oldest date for Duplicate Cust. #
I'm trying to get the oldest date associated with a customer number, and in the Cust# column, i'll have many duplications of the same customer number. Let's say A is "Date", and B is "Cust#". (I won't be able to allow my users to sort the data, so i'll need a formula that returns either the oldest date, or the cell which contains the oldest date.) Any help is much appreciated! Nevermind. I found it using Google/Groups. {=MIN(IF($B$1:$B$10=B1,$A$1:$A$10))} >-----Original Message----- >I'm trying to get the oldest date associated with a...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Dates #9
The problem of a date code... I need to address this so that fo example, 5/6/04 can be correctly entered as either 5th of June or 6t of May, depending from where the date emanted. regards -- Message posted from http://www.ExcelForum.com Couldn't you format the cell as mmmm dd, yyyy so that the user sees what date they entered in a non-ambiguous manner right away? Or maybe provide 3 inputs: Month, day, and year. You could combine them elsewhere. "adn4n <" wrote: > > The problem of a date code... I need to address this so that for > example, 5/6/04 can be c...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

date tracking
I am entering clients into a 2007 Excel spreadsheet. How do I make the entry turn color when 14 days have passed? Gene This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CAC8D4.5688AC60 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit As part of the "client" entries, do you enter the date the client was entered? This would be the key in doing this task. In a cell on the worksheet you could enter the formula for today's date like this =TODAY(). Then use the con...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Associating Multiple Existing Contacts to an Account, Until CRM 2.x
I have racked my pea-sized brain for endless days for a way to associate multiple existing contacts (or accounts) to an account. My client has accounts that have many contacts (or accounts) involved at different stages of acquiring a new account. The logical method would be to add a custom attribute with lookup functionality into the existing CRM contact or account objects but this is not possible with CRM 1.x. The resulting UI would allow an end user to associate different contacts (or accounts) to an account. Example: Account: ABC - AccountPhysician: (existing account or con...

question about Time
How to make the time result for example if it�s ( 1:01 ) or higher shows only as ( 1:00 ) and if it�s Lower like ( 0:59 ) or less it will show the same result in this case ( 0:59 ) Any idea & suggestions. Thanks, almufadda@hotmail.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Using Ron deBruin's google addin and asking for subject round time, I get http://tinyurl.com/wgua -- Don Guillett SalesAid Software donaldb@281.com "saud" <saud.xgc4...

Time Clock Systems
Does anyone have a recommendation for a time clock system that integrates well with GP? On Oct 5, 10:20 am, kcd <k...@discussions.microsoft.com> wrote: > Does anyone have a recommendation for a time clock system that integrates > well with GP? We just implemented Time Matrix by Business Computers (www.business- computers.com) and are very happy with it. We implemented quickly the hardware wasn't propietary or complicated so we were able to source our own stuff. Troy I can speak highly of Unitime's time and attendance system. They are a relatively low cost solution t...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

Adding a word to the end of other words at the same time
I was wondering if there was a way to add a word to the end or beginning of multiple other words in Excel. Example; say I have these 3 words.... Alpha Beta Tera Now I want to add LLC to the end of each word but I want to change them all at the same time. Like Alpha LLC Beta LLC Tera LLC Is there a way to do that? Phil Its Excel 2003 try Sub addtexttoend() For Each c In Selection c.Value = c & " xxx" Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "phil" <ptukey@charter.net> wrote in message news:1125340358.873337.4240@g44g2000cwa.googlegroup...

date modified
I have two sheets Data and Summary The "data" sheets macro extracts data from external file and paste into "Data" sheet Everytime the m acro is run to get latest data... The macro delete all contents of the "data" and then paste new data into the "data" sheet. Is there a way.. I can put a date on the "summary" sheet, when was the time the macro was run ( or in other words.. the data updated) This little macro records the date in the selected cell and formats it: Sub Macro2() Dim d As Date Dim s As String d = Now() s...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...