How can I set month/quarter/annual date intervals

I have data collected on a daily basis (for work days) over several years, 
and I want to plot it on an X-Y plot versus date. I would like the monthly 
intervals showing on the scale to be like 1/1/04, 2/1/04, 3/1/04, etc. 
Similarly, for annual intervals, 1/1/03, 1/1/04, 1/1/05, etc. Excel just 
wants to put in the same number of days for the intervals. Any suggestions?
0
John1063 (943)
3/14/2006 9:36:29 PM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
342 Views

Similar Articles

[PageSpeed] 55

It sounds like you are using an XY chart.

For XY trend charts, I often run into the issue you are talking about 
frequently.  I use a few formatting tricks:

For my X Axis number format I double click the X axis to get Format Axis 
window. I  select number tab and enter my date format. For monthly data 
spanning years, I use  "mmm'yy" which will show up as Mar'06 on the chart X 
axis.

For my interval, I use 32 days to make sure that I get my months to 
increment.

I have several trend chart examples at 
http://processtrends.com/pg_charts_trend_chart.htm

...Kelly

koday@processtrends.com




"John" <John@discussions.microsoft.com> wrote in message 
news:C59D798E-8749-4CD5-B5C8-AB77AC9EE4D9@microsoft.com...
>I have data collected on a daily basis (for work days) over several years,
> and I want to plot it on an X-Y plot versus date. I would like the monthly
> intervals showing on the scale to be like 1/1/04, 2/1/04, 3/1/04, etc.
> Similarly, for annual intervals, 1/1/03, 1/1/04, 1/1/05, etc. Excel just
> wants to put in the same number of days for the intervals. Any 
> suggestions? 


0
dkod (205)
3/14/2006 10:09:19 PM
Thanks, Kelly, but this did not work for me. When I used 32 days, after about 
every 20 months, it skipped a month on the axis, as if it were "rounding" to 
the nearest month.

I have an old chart that has on the x-axis, 1/4/99, 2/4/99, 3/4/99, etc 
through 8/4/04. Unfortunately all I have is a printed copy and have lost the 
Excel file from which it was printed. So I know it can be done, but I just 
can't figure it out.

"Kelly O'Day" wrote:

> It sounds like you are using an XY chart.
> 
> For XY trend charts, I often run into the issue you are talking about 
> frequently.  I use a few formatting tricks:
> 
> For my X Axis number format I double click the X axis to get Format Axis 
> window. I  select number tab and enter my date format. For monthly data 
> spanning years, I use  "mmm'yy" which will show up as Mar'06 on the chart X 
> axis.
> 
> For my interval, I use 32 days to make sure that I get my months to 
> increment.
> 
> I have several trend chart examples at 
> http://processtrends.com/pg_charts_trend_chart.htm
> 
> ...Kelly
> 
> koday@processtrends.com
> 
> 
> 
> 
> "John" <John@discussions.microsoft.com> wrote in message 
> news:C59D798E-8749-4CD5-B5C8-AB77AC9EE4D9@microsoft.com...
> >I have data collected on a daily basis (for work days) over several years,
> > and I want to plot it on an X-Y plot versus date. I would like the monthly
> > intervals showing on the scale to be like 1/1/04, 2/1/04, 3/1/04, etc.
> > Similarly, for annual intervals, 1/1/03, 1/1/04, 1/1/05, etc. Excel just
> > wants to put in the same number of days for the intervals. Any 
> > suggestions? 
> 
> 
> 
0
John1063 (943)
3/14/2006 11:59:26 PM
John:

How many months of data do you have to chart?

Have you tried 2 month interval (63 days)?
What about quarter (94 days)?


...Kelly










"John" <John@discussions.microsoft.com> wrote in message 
news:30E0FF84-F5C7-47D9-A08A-A0D082B200F6@microsoft.com...
> Thanks, Kelly, but this did not work for me. When I used 32 days, after 
> about
> every 20 months, it skipped a month on the axis, as if it were "rounding" 
> to
> the nearest month.
>
> I have an old chart that has on the x-axis, 1/4/99, 2/4/99, 3/4/99, etc
> through 8/4/04. Unfortunately all I have is a printed copy and have lost 
> the
> Excel file from which it was printed. So I know it can be done, but I just
> can't figure it out.
>
> "Kelly O'Day" wrote:
>
>> It sounds like you are using an XY chart.
>>
>> For XY trend charts, I often run into the issue you are talking about
>> frequently.  I use a few formatting tricks:
>>
>> For my X Axis number format I double click the X axis to get Format Axis
>> window. I  select number tab and enter my date format. For monthly data
>> spanning years, I use  "mmm'yy" which will show up as Mar'06 on the chart 
>> X
>> axis.
>>
>> For my interval, I use 32 days to make sure that I get my months to
>> increment.
>>
>> I have several trend chart examples at
>> http://processtrends.com/pg_charts_trend_chart.htm
>>
>> ...Kelly
>>
>> koday@processtrends.com
>>
>>
>>
>>
>> "John" <John@discussions.microsoft.com> wrote in message
>> news:C59D798E-8749-4CD5-B5C8-AB77AC9EE4D9@microsoft.com...
>> >I have data collected on a daily basis (for work days) over several 
>> >years,
>> > and I want to plot it on an X-Y plot versus date. I would like the 
>> > monthly
>> > intervals showing on the scale to be like 1/1/04, 2/1/04, 3/1/04, etc.
>> > Similarly, for annual intervals, 1/1/03, 1/1/04, 1/1/05, etc. Excel 
>> > just
>> > wants to put in the same number of days for the intervals. Any
>> > suggestions?
>>
>>
>> 


0
dkod (205)
3/15/2006 12:52:22 AM
Kelly,
It varies. Sometimes I have 5 years worth, so that would be 60 months. Since 
months, quarters, and years are of different lengths, having a certain number 
of days as the major unit on the scale doesn't work out. Is there a way to 
tell Excel to have as the major unit on the scale months or quarters or 
years, even though the data are plotted by days? This is a format axis issue, 
and I have done it before, but don't remember how.
John

"Kelly O'Day" wrote:

> John:
> 
> How many months of data do you have to chart?
> 
> Have you tried 2 month interval (63 days)?
> What about quarter (94 days)?
> 
> 
> ...Kelly
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> "John" <John@discussions.microsoft.com> wrote in message 
> news:30E0FF84-F5C7-47D9-A08A-A0D082B200F6@microsoft.com...
> > Thanks, Kelly, but this did not work for me. When I used 32 days, after 
> > about
> > every 20 months, it skipped a month on the axis, as if it were "rounding" 
> > to
> > the nearest month.
> >
> > I have an old chart that has on the x-axis, 1/4/99, 2/4/99, 3/4/99, etc
> > through 8/4/04. Unfortunately all I have is a printed copy and have lost 
> > the
> > Excel file from which it was printed. So I know it can be done, but I just
> > can't figure it out.
> >
> > "Kelly O'Day" wrote:
> >
> >> It sounds like you are using an XY chart.
> >>
> >> For XY trend charts, I often run into the issue you are talking about
> >> frequently.  I use a few formatting tricks:
> >>
> >> For my X Axis number format I double click the X axis to get Format Axis
> >> window. I  select number tab and enter my date format. For monthly data
> >> spanning years, I use  "mmm'yy" which will show up as Mar'06 on the chart 
> >> X
> >> axis.
> >>
> >> For my interval, I use 32 days to make sure that I get my months to
> >> increment.
> >>
> >> I have several trend chart examples at
> >> http://processtrends.com/pg_charts_trend_chart.htm
> >>
> >> ...Kelly
> >>
> >> koday@processtrends.com
> >>
> >>
> >>
> >>
> >> "John" <John@discussions.microsoft.com> wrote in message
> >> news:C59D798E-8749-4CD5-B5C8-AB77AC9EE4D9@microsoft.com...
> >> >I have data collected on a daily basis (for work days) over several 
> >> >years,
> >> > and I want to plot it on an X-Y plot versus date. I would like the 
> >> > monthly
> >> > intervals showing on the scale to be like 1/1/04, 2/1/04, 3/1/04, etc.
> >> > Similarly, for annual intervals, 1/1/03, 1/1/04, 1/1/05, etc. Excel 
> >> > just
> >> > wants to put in the same number of days for the intervals. Any
> >> > suggestions?
> >>
> >>
> >> 
> 
> 
> 
0
John1063 (943)
3/15/2006 3:54:27 AM
Kelly, 
Success! Whoopee!
I finally figured it out. Instead of an X-Y chart, use a Line chart. Then I 
can have the major units as months.
John

"Kelly O'Day" wrote:

> John:
> 
> How many months of data do you have to chart?
> 
> Have you tried 2 month interval (63 days)?
> What about quarter (94 days)?
> 
> 
> ...Kelly
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> "John" <John@discussions.microsoft.com> wrote in message 
> news:30E0FF84-F5C7-47D9-A08A-A0D082B200F6@microsoft.com...
> > Thanks, Kelly, but this did not work for me. When I used 32 days, after 
> > about
> > every 20 months, it skipped a month on the axis, as if it were "rounding" 
> > to
> > the nearest month.
> >
> > I have an old chart that has on the x-axis, 1/4/99, 2/4/99, 3/4/99, etc
> > through 8/4/04. Unfortunately all I have is a printed copy and have lost 
> > the
> > Excel file from which it was printed. So I know it can be done, but I just
> > can't figure it out.
> >
> > "Kelly O'Day" wrote:
> >
> >> It sounds like you are using an XY chart.
> >>
> >> For XY trend charts, I often run into the issue you are talking about
> >> frequently.  I use a few formatting tricks:
> >>
> >> For my X Axis number format I double click the X axis to get Format Axis
> >> window. I  select number tab and enter my date format. For monthly data
> >> spanning years, I use  "mmm'yy" which will show up as Mar'06 on the chart 
> >> X
> >> axis.
> >>
> >> For my interval, I use 32 days to make sure that I get my months to
> >> increment.
> >>
> >> I have several trend chart examples at
> >> http://processtrends.com/pg_charts_trend_chart.htm
> >>
> >> ...Kelly
> >>
> >> koday@processtrends.com
> >>
> >>
> >>
> >>
> >> "John" <John@discussions.microsoft.com> wrote in message
> >> news:C59D798E-8749-4CD5-B5C8-AB77AC9EE4D9@microsoft.com...
> >> >I have data collected on a daily basis (for work days) over several 
> >> >years,
> >> > and I want to plot it on an X-Y plot versus date. I would like the 
> >> > monthly
> >> > intervals showing on the scale to be like 1/1/04, 2/1/04, 3/1/04, etc.
> >> > Similarly, for annual intervals, 1/1/03, 1/1/04, 1/1/05, etc. Excel 
> >> > just
> >> > wants to put in the same number of days for the intervals. Any
> >> > suggestions?
> >>
> >>
> >> 
> 
> 
> 
0
John1063 (943)
3/15/2006 4:30:11 AM
Reply:

Similar Artilces:

Can I fade the edges of photos that I use?
I am trying to use photos in a publisher document that I want to fade the edges of, is this possible to do with publisher? -- D:o) krazy-4-coke wrote: > I am trying to use photos in a publisher document that I want to fade the > edges of, is this possible to do with publisher? Not really. You should use dedicated image manipulation software for this, such as Photoshop Elements, Paint Shop Pro, or Microsoft Digital Image. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org Ed, Are any of these programs typically come loaded on a computer?? I have another question - ca...

Excel 2007 Need to permanently change Normal settings for gridlines
I am using Excel 2007 for the first time and find the gridlines delineating the cells are so faint as to be nearly indistinguishable. I can go into the cell formatting and modify the normal style, and it is just the way I want it. But I can't find a way to make Excel remember this and treat it as the new definition of the Normal style. I don't want to have to redefine Normal every time I open a new spreadsheet. Someone please help! Regards Leonard Priestley The changes you describe are changing the Border color and NOT gridlines. Go into Excel Options and cl...

CRM do not recognize different character set encoding for incoming e-mail???
I have a problem with some of incoming e-mail. If incoming e-mail character set is not unicode but for example Cyrillic then I dont see russian letters, but some scrawl!!! My Outlook recognize all what I need. Whats about CRM??? Sorry about my poor english. ...

Can I delete my switchboard and make a new one?
I do not like my switchboard and would like to delete this one and start all over. But when I did delete it I couldn't make another one, because I couldn't delete the default. What should I do?Thanks,Dustin Just delete the form and the table that were created by the wizard, then start over.-- Rick B"Dustin" <Dustin@discussions.microsoft.com> wrote in message news:42F94A44-9352-4D02-9104-65593869B271@microsoft.com...>I do not like my switchboard and would like to delete this one and start >all> over. But when I did delete it I couldn't make another one, beca...

Cannot set menu in MFC ActiveX control
I am dynamically creating a CMenu object and trying to assign it to my ActiveX control. Windows balks and gives me a 1436 error, something to effect that a child window cannot have a menu. It is not a child window, it is the instance of COleControl, the top level window. (I verified this by inspecting m_parentHwnd.) I am trying to assign the menu within the OnCreate event at which time the control has a valid handle, and have tried it before and after the call to the parent OnCreate. Does anyone know the magic words to make this work? Thanks On Mon, 8 Dec 2008 16:22:02 -0500, Duane A. C...

pivot table -repeating "months or days" after grouping
I have data that includes the month, day, hour, min. When I run th pivot table function to reduce the data to hourly I need the date t repeat in the date column. Example: 9/19/2002 0:00 2.7 9/19/2002 0:10 3.7 9/19/2002 0:20 3.8 9/19/2002 0:30 4.6 9/19/2002 0:40 4.8 9/19/2002 0:50 4.8 9/19/2002 1:00 5.6 9/19/2002 1:10 5.2 What I get when I group the Pivot table: Sep 19-Sep 12 AM 2.7 - - 1 AM 3.7 - - 2 AM 3.8 - - 3 AM 4.6 - - 4 AM 4.8 What I want is: Sep 19-Sep 12 AM 2.7 - 19-Sep 1 AM 3.7...

how can I drag formulas with other cell references
example cell A1 contains formula "=stdev(a2:a7) cell a2 contains formula "=stdev(a8:a13) Is it possible to drag the formula down so that cell a3 contains "=stdev(a14:19) and cell a4 contains "=stdev(a20:a25) and so on for cells a5, a6, a7 etc. etc. Hi Bram, See http://www.mvps.org/dmcritchie/excel/snakecol.htm#snkAddr B1: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0)) B2: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0)) It was easier to test and verify using SUM instead of stdev, but the formula is same NOTE the formulas are in a different colu...

Date Range Formula Question
Hello, I'm having trouble with a formula and I'm hoping someone can help. :confused: Sample Data Includes the following: Pay Period Start Pay Period End Pay Period # 12/16/01 12/29/01 26 12/30/01 01/12/02 25 01/13/01 01/26/02 24 01/27/02 02/09/02 23 02/10/02 02/23/02 22 The pay periods continue until there are 26 pay periods for the entire year....

can i have more sender in outlook setting ?
i using win server 2003 , exchange 2003 , i using exchange client in outlook to send & receive , but my boss have more than one mail address , can i use another AD mail address for send out mail , i have try to add a pop3 , the sender also is computer user thank your ...

IE8 can't access Microsoft Office site when all other browsers can
When trying to access the Microsoft Office page using IE8 I keep getting the "Internet Explorer cannot display the webpage" message. If I use any other browser (Firefox, Opera, Safari, Chrome) I have no problem accessing the page. This happens whether running in normal or No Add-on's mode. Despite how many people keep complaining about all the problems with IE8, neither the MVP's or Microsoft acknowledge that they exist. Asus P5E Intel E8400 Core2Duo 3.0GHz 4 GB PC2-6400 DDR2 Windows 7 Ultimate (with all the latest updates installed) NIS 2010 (all up to date)...

I can't get rid of 1 "Payment to Send' Reminder-M07
On my Home Page in the Reminder section, I have a "1 Payment to Send" listed there. But there is NO payment to send. I"ve opened up back-up copies as far back as May and the reminder is there, but there is no payment that needs sending. Does anybody have an idea how I can get rid of this Reminder? Just removing the Reminder from Home Page doesn't do it. When you put the Reminder back on the Home Page, the "Payment to Send" is still there. Clicking on the link just takes me to Bill Summary page. ...

Time Zone / Incorrect Dates Displayed to User
When a user in the Central Time zone closes an opportunity and someone in the Mountain Time zone views it, the Actual Close Date is 1 day before the date the person in the Central Time zone selected. For instance... If someone in the Central Time zone closes an opportunity and selects the Actual Close Date as 6/2/2007: -- Users in the Central Time zone see: 6/2/2007 -- But users in the Mountain Time zone see: 6/1/2007 This is a big issue when running monthly reports as it makes some opportunities show in the wrong month... and will put that revenue in the wrong month. It would make mo...

is there any way I can trap for the use of navigation buttons?
Hi Is there any way I can trap for the use of navigation buttons, such as Next, Previous and New record? Stapes Probably. Trap what and when? "Stapes" <steve.staple@gmail.com> wrote in message news:7fe0fd6b-0da1-495e-a369-29bc72ca3af9@s8g2000prg.googlegroups.com... > Hi > > Is there any way I can trap for the use of navigation buttons, such as > Next, Previous and New record? > > Stapes On Wed, 16 Jan 2008 05:14:20 -0800 (PST), Stapes <steve.staple@gmail.com> wrote: No. And there shouldn't be a reason for that. There are plenty of events in...

how can I look into store.exe
I have an email sobig that i cant even download it on my outlook express 6.0.... The adviced for is to locate it on the Queues in Outbound or inbound messages on IMC, but if it is not in there the message may be stuck up in Store. How can I locate it and delete the massega in store.exe they say I can use the MFCmapi but i'm a newbie how can do this. Do I have to download the MFCMapi? what is it? thanks ...

can't change/delete bills and deposits
suddenly one day i realized that when i tried to change a bill, i could click "ok" but nothing would happen. I thought it was because i upgraded to 2004, so i deleted the bills and rescheduled. about 5 of the bills will not delete either. the error message is: "Money could not write to your Money file. The operation could not be performed or another application finished this task before you. Please try again." Now some of the newly scheduled bills will not allow me to change the properties, i click ok but nothing happens when i edit the series. I have repaired ...

Can't Install IBF for CRM on CRM Server
first i'm install IBF On my CRM v1.2 (it's name testcrm) after that i'm try to install IBF for CRM on my CRM Server but in Step "Publish Metadata" i'm type "http://testcrm:8082" and click next button but it's show message box "The Information Bridge metadata service location is not avaliable" and can't install it. -_-' . but i can access "http://testcrm:8082/IBFWriteService.asmx" and "http://testcrm:8081/IBFReadService.asmx". so how can i install it ? I had the same problem - try using http://localhost:8082 for...

"Object variable or With block not set."
-- RiverCrescent On Sun, 14 Mar 2010 16:22:01 -0700, CedarPoint <CedarPoint@discussions.microsoft.com> wrote: You are referencing a variable incorrectly or misspelling it. If you would like help fixing your code... post your code. We can't see it from here. -- John W. Vinson [MVP] ...

Can Work thread get a windows class and manipulate it?
Suppose that I create a work thread when my dialog box started, then in the thread I get the pointer to the dialog mfc class and call updateData method. Does that work? I tried, but failed. Is there any other way to do? thanks!! No, it won't work, don't even waste time trying. It is almost guaranteed to fail. You do not manipulate the windows owned by one thread from a different thread. What you do is PostMessage requests from the worker thread to the main UI thread, usually to the window that contains the controls. The fact that you are using UpdateData already says you are in t...

adding date/time picker to inputbox control
Hi all,is there a way to add a date/time picker control to inputbox?...

Why can't I send email messages with Trial Outlook 2007?
I have a new HP laptop that came with the 60 day trial of Office'07. I receive email but the send/reply/compose functions are grayed out. I am certain my settings are correct. Why can't I send email? KK What happens when you use the Test Account Settings? What is the exact error message you get? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, KK asked: | I have a new HP laptop that came with the 60 day...

How can I keep the header in view at all times
In Word, a header and footer s always in view when you work in th document. However, I cannot seem to do the same in Excel - any suggestions please. Thanks - M Select a cell below your column header and just right of your ro header, then goto "Window" and select Freeze Panes. HTH J -- pinmaste ----------------------------------------------------------------------- pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=626 View this thread: http://www.excelforum.com/showthread.php?threadid=49759 ...

The code below worked for a few days and just totally stopped working. What can I do to make sure it continues to work? Thanks!
The code below worked for a few days and just totally stopped working. What can I do to make sure it continues to work? Thanks! ----- Private Sub Worksheet_Change(ByVal Target As Range) Dim C As Range, D As Range Set D = Intersect(Range("A:A"), Target) If D Is Nothing Then Exit Sub For Each C In D On Error Resume Next Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]" ' - For Column E Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]" ' - For Column F Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,I.O.! R2C1:R5...

Someone must have asked this before but I can't find it!
Being a relative Excel newcomer I,m not sure of all the terminology so may have used the wrong terms when searching. What I am trying to do is create a worksheet in which a cell shows one of 3 values (LOW,AVERAGE or HIGH) according to the value in the cell to the left of it, which itself is the sum of the 2 preceding cells. To be more specific, cell D6 is Men, E6 is Women and F6 is"=SUM(D6,E6)". I then want G6 to show "LOW" if F6 is less than 5, "AVERAGE" if it is between 5 and 20, and "HIGH" if it is over 20. If anyone understands the question and can h...

Print Full Bleed in Publisher? Can I eliminate the non-printable a
Hi there I have a "Print Merge" document I am printing. It's basically tickets 6 up on a page and I'm using Print Merge to number the tickets. Also have it set up to print multiple (page size is 2.75inch by 5.5inch). I have the margins set to ZERO and the horizontal, and sides set to ZERO as well. My printer supports full bleed and I can print full bleed from this PC using the same driver with all other programs (Adobe Illustrator, Photoshop, etc, etc), just not with Publisher. I've checked the forums here and can't seem to find a solution. I've selected t...

Add working hours to date field
We offer computer support based on a certain response time in hours. I want to have a date field, on the case form, to automatically set x WORKING hours ahead, usually 8 hours. The actually hour value will be pulled from a contract. Does anyone know if this is possible or am I shooting way too high? ...