I cannot set the maximum date for the axis of a line chart

I have a line chart with a (horizontal) date axis along the bottom.  
Currently I have data from Sep-2002 to Sep-2009.  All of this data is shown 
on the chart and the axes also go from Sep-2002 to Sep-2009.

My problem is that I want to set the maximum of the date axis to an earlier 
date (say Jun-2008) hence hiding the later part of the data (without deleting 
the original data).  But Excel 2007 will not let me do this (apparently).

I have selected the axes and fixed the "maximum" date at an earlier date 
(like Jun-2008).  Surprisingly, Excel leaves the axis exactly the same (it 
still ends at Sep-09) but all the date labels beyond Jun-2008 have 
disappeared.  Weird!

The other weird thing is that if I try and fix the "minimum" date then it 
works as I want it to.  For example, I can fix the "minimum" to Sep-03 and 
Excel will hide the first year of data.  But for some reason it won't give me 
the same level of control over the maximum!?!

Any suggestions?

Usually I can find a work-around for these sorts of things, but I am really 
stumped so would appreciate insight from the experts.

p.s. I should note that I am using a line graph with a secondary vertical 
axis.  I think this may be part of the problem.  It seems that if I eliminate 
the data on the secondary vertical axis then Excel gives me back control over 
"maximum" dates.

0
Utf
3/4/2010 1:09:01 PM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
1459 Views

Similar Articles

[PageSpeed] 14

When I first started using Excel 2007, I had the same problem: could not 
type a date into the boxes setting the axis scale. The solution is to type 
the serial number of the date. On the worksheet enter the required date in 
the normal way; format the cell General so that you see a 5-digit number 
like 40123. Enter this value into the Format Axis box.
I do not use dates that much but I now find that I can enter a date into the 
scale boxes. I cannot recall when this changed. Do you have the second 
service pack (SP2) installed?
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"David" <David@discussions.microsoft.com> wrote in message 
news:2C48C9E5-B3FC-44BE-B3B5-2D34EBF4F529@microsoft.com...
> I have a line chart with a (horizontal) date axis along the bottom.
> Currently I have data from Sep-2002 to Sep-2009.  All of this data is 
> shown
> on the chart and the axes also go from Sep-2002 to Sep-2009.
>
> My problem is that I want to set the maximum of the date axis to an 
> earlier
> date (say Jun-2008) hence hiding the later part of the data (without 
> deleting
> the original data).  But Excel 2007 will not let me do this (apparently).
>
> I have selected the axes and fixed the "maximum" date at an earlier date
> (like Jun-2008).  Surprisingly, Excel leaves the axis exactly the same (it
> still ends at Sep-09) but all the date labels beyond Jun-2008 have
> disappeared.  Weird!
>
> The other weird thing is that if I try and fix the "minimum" date then it
> works as I want it to.  For example, I can fix the "minimum" to Sep-03 and
> Excel will hide the first year of data.  But for some reason it won't give 
> me
> the same level of control over the maximum!?!
>
> Any suggestions?
>
> Usually I can find a work-around for these sorts of things, but I am 
> really
> stumped so would appreciate insight from the experts.
>
> p.s. I should note that I am using a line graph with a secondary vertical
> axis.  I think this may be part of the problem.  It seems that if I 
> eliminate
> the data on the secondary vertical axis then Excel gives me back control 
> over
> "maximum" dates.
> 
0
Bernard
3/4/2010 2:21:58 PM
You could always use dates in a line chart's dialog, but never in an XY 
chart's (still can't in 2007 SP2). Microsoft did finally enable date 
entry in XY chart axis dialogs in 2010.

I don't know what is the user's problem, but installing SP2 often fixes 
things like this.

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


On 3/4/2010 9:21 AM, Bernard Liengme wrote:
> When I first started using Excel 2007, I had the same problem: could not
> type a date into the boxes setting the axis scale. The solution is to
> type the serial number of the date. On the worksheet enter the required
> date in the normal way; format the cell General so that you see a
> 5-digit number like 40123. Enter this value into the Format Axis box.
> I do not use dates that much but I now find that I can enter a date into
> the scale boxes. I cannot recall when this changed. Do you have the
> second service pack (SP2) installed?
> best wishes
0
Jon
3/4/2010 3:45:14 PM
Thanks for the replies guys.  

I do have SP2 installed for Office 2007.  I am also able to enter in dates - 
as I mentioned, I am able to set the minimum date to whatever I want.  But 
not the maximum date.  Note that I have also used the 5-digit number approach 
and I still can't set the maximum date.

By the way Jon, your website is an excellent resource of charting tips and 
techniques.  Whenever I need to produce a tricky graph I go to your website 
first.  I want to thank you for creating it.

Cheers
David

0
Utf
3/5/2010 1:01:02 AM
I have done some further exploration of my problem.  I think this may just be 
a glitch with secondary axes line charts in Excel 2007.  

I can show this using some "made-up" data.  I entered the following data 
into Excel 2007:

Date      Series 1    Series 2
1	43	10
2	45	11
3	45	14
4	67	16
5	89	13
6	90	#N/A
7	89	#N/A
8	88	#N/A

I then used this data to create line chart.  I made the axis a date axis.  
And I put the second series on the second vertical axis.  

I then changed the maximum date to "5" and Excel 2007 merely removes the 
labels.  It does not actually change the maximum date!

I note that if I just create the chart with Series 1 on it then I can change 
the maximum date.

So it looks to me like it is a glitch!  Or am I doing something wrong?  Its 
quite frustrating because I often use graphs with secondary axes.

0
Utf
3/8/2010 3:43:02 AM
Using two variations, without a secondary X axis and with a secondary X 
axis, I was able to change the maximum by selecting Fixed and entering 
1/5/1900. Entering just 5 does not work in 2007, because they forgot 
that even in axis scale input boxes, numbers can have different formats.

Make sure you have SP2 installed. The original release of 2007 had lots 
of chart glitches, SP1 solved many, SP2 solved many, and many remain.

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


On 3/7/2010 10:43 PM, David wrote:
> Date      Series 1    Series 2
> 1	43	10
> 2	45	11
> 3	45	14
> 4	67	16
> 5	89	13
> 6	90	#N/A
> 7	89	#N/A
> 8	88	#N/A
0
Jon
3/9/2010 2:51:51 PM
Reply:

Similar Artilces:

Hand entered dates view wrong
A user I support is having an issue with a worksheet in Excel. It is not an urgent or crucial fix, but I am interested in people's ideas, since she says it has happened to her before. In each case she threw away the worksheet and started a new one by hand entering the data again. Here's the scenario: She has a worksheet in which she has been adding rows of data over time (6 months or so) and has only about 30 rows of data. Column A are dates, and are formatted as such. After months of opening and saving in this document once a week or so, she now has an issue - when she types...

set limit for one year prior
trying to write a query to select occupdate for one year prior to toustartdate where occupdate < (toustartdate - 365) where occupdate < (toustartdate - 1 year) don't work I'm sure it's a simple solution - can someone help me out. Ian (Ian@discussions.microsoft.com) writes: > trying to write a query to select occupdate for one year prior to > toustartdate > > where occupdate < (toustartdate - 365) > where occupdate < (toustartdate - 1 year) > don't work These WHERE clauses would return rows where occupdate is one or more bef...

charting independent XYScatter graphs
I'm trying to create a XYScatter graph of 3 independent datasets from within VB6 using OLE/Excel2000. I'm using the KB147803 article as a basis, but am stuck and hope someone can help. The example in KB147803 seems to indicate that there can only be one set of X-Values for the 3 XY Scatter Graph data sets. The X-Value series is the first row of data in the sheet, and is called out as a set of Category labels ('cwCategoryLabels=1'), and applies to all three subsequent rows of Y-Value data. In my case, I want to plot 3 independent sets of XY data, so I'd like to pr...

Need to set up a slide with 4 text boxes on same page.
Want to end up with 4 "bulleted" boxes that I can to show 4 strategies and associated task on same page. Also, if possible have each one drop in individually to allow flow conrol for the presentation Are you asking a question about how to do that or having trouble with part of that? If the former, just create four separate text boxes with bulleted text, you are not limited to only one text box per slide. Use the Custom Animation, Effects Options, Text Animation settings to control the entrance of the bullet points. Since you didn't say what version of PowerPoi...

Help with graph / chart
I have a graph for weeks 1-52, I have split this into 4 seperate graphs each showing a quarter (13 weeks) I cant remember exactly how I created them but possibly using some sort of copy paste as each chart show weeks 1 - 13 along the bottom. This should read......... for chart 1 1-13 for chart 2 14 - 26 chart 3 27 - 40 chart 4 41 - 52 How do I change this on each chart to read the week numbers indicated.? thanks Hi, You need to define the Category labels for the chart. Chart 1 is fine as it defaults to the values 1 to 13. For the other 3 charts you will need to create...

How do make a line correspond to date
I have a chart with horizontal bars and I want to put a vertical (trend) date line that corresponds to today's date. -- DMM http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "soconfused" <soconfused@discussions.microsoft.com> wrote in message news:ADC1BD32-DF02-4A7F-B074-DCBBA9D0AAFD@microsoft.com... >I have a chart with horizontal bars and I want to put a vertical (trend) >date > line that corresponds to today's...

Label lines disappear when I copy to Powerpoint
Just created a pie chart in Excel 2007 with Labels which have a line joining the label to the pie segment, however when I copy and paste into Powerpoint the lines joining the labels to the segments disappear. Anyone know how to fix this? Need presentation for tomorrow! __________ Information from ESET Smart Security, version of virus signature database 4031 (20090423) __________ The message was checked by ESET Smart Security. http://www.eset.com ...

Copy chart with reference table
I have a pie chart neatly tied to a data table alongside. Now I want to copy both the chart and table to a different area of the sheet so I can modify the new data table and have 2 different charts. My issue is that the chart will only allow absolute references, so when I copy both the chart and table together the new chart still is tied to the old table - so I have to go and change the source data ranges one by one. Any way that I can do this easily? Copy the whole sheet. The copied chart links to the data on the copied sheet. Now cut the range that includes the copied char...

Date/Time Modified
Hello, My Access Database consists of many tables. The Query attached to my main Client Information Form is made up of 4 tables. -Client Table -Account Table -Batch No Table -Advisor Table Basically, I want to be able to insert a Date/Time Modified record at the top of my main form that will show a data modified if any record is changed/updated in any of these tables. Is this possible? Thank you, Mellissa -- Message posted via http://www.accessmonster.com ...

Excel 2007 dates and conditional formats
Can anyone please tell me why I can not use a conditional format (<>=) to identify if a date is greater or smaller then another. Everytime I try it, Excel does it right in the first column but misses by 2 days in all following. It also appears to have problems with the years. Example: Date of propposed compleation of a task is 10/10/07; if the date of actual completion is 10/10/07 or sooner, turn GREEN. If the date of completion is 10/11/07 or older, turn RED. Can anyone help?? Regards Mary Hi Mary, First thing to check: What does 10/11/07 mean? November 10 1907? November 10 2007? O...

setting up outlook 2000 with hotmail
I can't get my outlook to accept hotmail--the help section says "Start Outlook, and on the Tools menu, click E-mail Accounts.In the E-mail Accounts dialog box, click to select the Add a new E-mail Account option, and then click Next.In the Server Type dialog box, click HTTP." there is no E-mail Accounts tab, no Add a new E-mail account option , and no HTTP option. Can anyone tell me what to type in to the lines requesting incoming mail (pop3) and outgoing mail (smtp)? 1) Look on Help, About. It sounds like you have corp mode. You need IMO mode to have accounts. 2) Does ...

Button To Print Workbook With Certain Settings
hi, in a workbook i have 52 numbered sheets. they are numbered 1, 2, 3, 4 etc. on the first sheet i have two buttons. i need some coding help with the buttons. basically i want the first button (called even) to print all the even sheets in the workbook. and the second button will print the odd sheets in the workbook. also i want different margin settings for each button. the even button will need a left margin of 0.5 and the odd button will need a right margin of 0.5 - while all the other margins remain 0.2. i am hoping that this is possible. i tried recording the marcos but i am not...

unable to set a record as private (activities)
Microsoft CRM v3 Customers and prospects are requesting the ability to mark a record (primarily notes and emails) as private such that only the owner and other internal party users on the record are able to open it. ---------------- 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 the message pane. http...

Date Range #2
Hi, Can someone please assist me with the following issue: I am currently using the formula noted below to direct another application ( Bloomberg ) to select prices from the previous 6th business day. My question is> Is there any way to revise the formula to exclude US holidays for successive years (i.e. for 2005, 2006...) without having to manually change the date entries each year, in the formula? =WORKDAY(Today(),-6, {"5/31/04","7/5/04","9/6/04","11/25/04","12/25/04","1/1/05" }) Thank you I'd recommend listing...

XY Chart
I have created a short VBA macro that reads x and y data from a spreadsheet, where every row represents a new series. Besides the x and y data colums, I also have columns that specify the size of the marker, the markerstyle, and the foreground and background colors. I have adapted some of John Walkenbach's code to label each of the datapoints using .name. The x axis is basically an integer 1 to 5 and the y axis is a value like price or variable margin. There usually are multiple datapoints on any given integer on the x axis. For example, I may be plotting the price that a custom...

sorting by date
I have a sheet with date amount description 12.12.06 5.00 PLUMBER 04.02.01 50.00 ELECTRICIAN ETC How can I sort into date order. When I try I get all the 2001, 2006, 2005 together but not 2001, 2002, 2003,2004 Cananyone help please Is 12.12.06 text or a date value? In a blank cell, enter ISTEXT(A1) where A1 is a cell reference to this date. True means it is text and 03.01.07 will come before 12.12.06. You can set up a helper cell, enter =DATE(RIGHT(A1,2),LEFT(A1,2), MID(A1,4,2)) where I'm assuming 12.12.06 is month/day/year. The parameters...

how do i set up outlook using my existing internet email account?
I am trying to set up my existing internet email account with Outlook. It was previously set up, incorrectly. I have no idea what to do. On Mon, 27 Feb 2006 13:18:29 -0800, Clayton wrote: > I am trying to set up my existing internet email account with Outlook. It was > previously set up, incorrectly. I have no idea what to do. Ask your ISP. They should be able to talk you through. -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk ...

chart
Help... Is is possible to insert a header and/or footer into multiple charts(Excel 2000 or 2002)? I was hoping I could insert a common chart header/footer as is possible with worksheets. Any help appreciated. You could set up the headers and footers on one chart (File menu > Page Setup), then before doing anything else, select each subsequent chart in turn and press the F4 key, which repeats this action. You could also select a chart, then record a macro while setting up its headers and footers. Then edit the macro code to only do the headers and footers, and leave out all that...

Change color on date field in form for re-certification
My main form has two different date fields on it. One date field needs to be re-certified every 6 months, and the other field needs to be re-certified every 12 months. I would like each of those fields to automatically change color depending on how much time as elapsed since that training. For the 6 month field, months 1-5 should be green, 1 month left should be yellow and anything expired should be red. For the 12 month field, months 1-11 should be green, 1 month left should be yellow, and anything expired should be red. Thank you in advance for your help! messingerjc wrote...

Charts #37
Hi All, Can you please advise if we can create one chart with multiple pivot table of the same data. Thanks Niranjan ...

Macro to update information if date changes
using Exel 2000.. I want excel to compare dates on two seperate worksheets and if the date on the first is greater than the second to insert rows and than copy cells on the second to different cells also on the second page. ie..main sheet date is 11-05-08 compared to sheet2 date is 11-04-08...insert cells at a21:d21 and shift cells down (on sheet2) then copy cells b13:h13 to a21:b21 The description seems a bit inconsistent, but here is a shot With Worksheets("Main Sheet") If .Range("A1").Value > Worksheets("Second Sheet").Range("A1&q...

Update Charts X-axis
I have a program with 76 charts. They all use the same range to get their x-axis (dates). At the first of every month, I change the dates to show the upcoming month. The table shows the entire year, but we just view it one month (fiscal) at a time. The series is by rows, but could be changed to columns if necessary. I inherited this program. Currently I have to update each one of them manually (click on each and change their XValues). Is there a way I can update all of them at one time? Could vba be used to select and update each chart with me just selecting the range? I am no stranger...

Fixed straight line(s) on a chart
How can I draw a fixed straight line in an excel chart. A line on a level I can easily adjust changing the value in a spreadsheet cell, for example. Is it possible to fix even multiple lines (eg. as a fixed "bandwidth information" - data comparing to a bandwidth)? Thanks Lukas Hi See the following sites: http://andypope.info/charts/averageline.htm http://www.tushar-mehta.com/excel/charts/straight_lines/index.html http://peltiertech.com/Excel/Charts/DummySeries.html#AddLine -- Regards Frank Kabel Frankfurt, Germany "Lukas" <lukas.bieri@gmx.net> schrieb im Newsbeit...

How do i set up specific fields in Outlook contacts
I want to find out how I go about setting up an additional field in my outlook contacts. (2003 edition) I am looking to put a "home address" field underneath the business address field and I cannot figure out how to create this. I know that I can switch the business to home or other, but would really like to create some new fields here. Can someone tell me how? Thanks, Matt ...

Creating a square scatter chart
I am trying to create a scatter chart, but it needs to be square (i.e., 1 X unit takes the same amount of space as 1 Y unit). Is there a way to force the chart to be square? Jon Peltier has instructions on his web site for squaring gridlines: http://www.geocities.com/jonpeltier/Excel/Charts/format.html#SqrGrid Gene wrote: > I am trying to create a scatter chart, but it needs to be > square (i.e., 1 X unit takes the same amount of space as 1 > Y unit). Is there a way to force the chart to be square? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contexture...