Line Chart-EXACT End Date

This has come up several times on "tests" I have taken for jobs.  I have 
gotten this to work in the past by playing with the scale but I am sure there 
is a better way to do it.

I want to create a line chart based on dates where the end date shows on the 
axis.  The data doesn't have a common denominator that makes sense so I am 
stumped.  If I had my choice I would use a Scatter as I can make those dates 
behave but I need to do it with a Line Chart.

Have tried the Time/Scale axis as well as the Category option.

Thanks.
0
JoanNYC (4)
2/2/2007 7:42:01 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
677 Views

Similar Articles

[PageSpeed] 38

One easy way in a line chart is to sort the worksheet data into descending 
order by date. Make the line chart with a category, not time scale, axis. 
Double click the axis, and on the Scale tab, choose Categories in Reverse 
Order and if desired Value (Y) Axis Crosses at Maximum Category.

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


"Joan NYC" <JoanNYC@discussions.microsoft.com> wrote in message 
news:E844BA99-CC2F-4995-A3B8-847830A55DB5@microsoft.com...
> This has come up several times on "tests" I have taken for jobs.  I have
> gotten this to work in the past by playing with the scale but I am sure 
> there
> is a better way to do it.
>
> I want to create a line chart based on dates where the end date shows on 
> the
> axis.  The data doesn't have a common denominator that makes sense so I am
> stumped.  If I had my choice I would use a Scatter as I can make those 
> dates
> behave but I need to do it with a Line Chart.
>
> Have tried the Time/Scale axis as well as the Category option.
>
> Thanks. 


0
jonxlmvpNO (4558)
2/2/2007 7:50:25 PM
Thanks Jon

I will try it

Regards, 
Joan (still unemployed)

"Jon Peltier" wrote:

> One easy way in a line chart is to sort the worksheet data into descending 
> order by date. Make the line chart with a category, not time scale, axis. 
> Double click the axis, and on the Scale tab, choose Categories in Reverse 
> Order and if desired Value (Y) Axis Crosses at Maximum Category.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
> 
> 
> "Joan NYC" <JoanNYC@discussions.microsoft.com> wrote in message 
> news:E844BA99-CC2F-4995-A3B8-847830A55DB5@microsoft.com...
> > This has come up several times on "tests" I have taken for jobs.  I have
> > gotten this to work in the past by playing with the scale but I am sure 
> > there
> > is a better way to do it.
> >
> > I want to create a line chart based on dates where the end date shows on 
> > the
> > axis.  The data doesn't have a common denominator that makes sense so I am
> > stumped.  If I had my choice I would use a Scatter as I can make those 
> > dates
> > behave but I need to do it with a Line Chart.
> >
> > Have tried the Time/Scale axis as well as the Category option.
> >
> > Thanks. 
> 
> 
> 
0
JoanNYC (4)
2/2/2007 10:19:01 PM
Worked perfectly

Thanks again

j

"Jon Peltier" wrote:

> One easy way in a line chart is to sort the worksheet data into descending 
> order by date. Make the line chart with a category, not time scale, axis. 
> Double click the axis, and on the Scale tab, choose Categories in Reverse 
> Order and if desired Value (Y) Axis Crosses at Maximum Category.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
> 
> 
> "Joan NYC" <JoanNYC@discussions.microsoft.com> wrote in message 
> news:E844BA99-CC2F-4995-A3B8-847830A55DB5@microsoft.com...
> > This has come up several times on "tests" I have taken for jobs.  I have
> > gotten this to work in the past by playing with the scale but I am sure 
> > there
> > is a better way to do it.
> >
> > I want to create a line chart based on dates where the end date shows on 
> > the
> > axis.  The data doesn't have a common denominator that makes sense so I am
> > stumped.  If I had my choice I would use a Scatter as I can make those 
> > dates
> > behave but I need to do it with a Line Chart.
> >
> > Have tried the Time/Scale axis as well as the Category option.
> >
> > Thanks. 
> 
> 
> 
0
JoanNYC (4)
2/3/2007 1:44:00 PM
Reply:

Similar Artilces:

Cutting a line in a line chart when data series stops
I have a series like so: Jan 10 Feb 10 Mar 11 Apr 12 May Jun Jul And so on - the rest of the year is blank. When I draw a line chart for the full 12 months the series draws a line up to April, then plummets to zero in May. I want the line to just stop at April. If I use a series as above and genuine empty cells for the end of the year this ok, but since my May cell reads =IF($A11<=$D$17,G11,"") the "" does not seem to get recognised as a blank cell. Please help!! Change your formula to =IF($A11<=$D$17,G11,NA()) -- David Biddulph "DannyS" <Da...

Money 2007 -- QIF import
Good Evening! After a little trial and error, I am able to import account transactions from another system (not a bank statement, my actual register transactions) with little trouble except for the date format. (Before someone gets mad at me, I really don't think this has anything to do with my regional settings!) As recommended by an MVP from this forum, I open my CSV file with excel, then use a converter (add-in) called XL2QIF to create a qif output file. The actual "data" in a date field in my original csv file is "060106" (mm/dd/yy) which is June 1, 2006,...

All charts in gray color
In SharePoint Sever after I upload a excel file all my charts are displayed in single gray color, not even a different tones of gray just one tone, any idea how to correct that behavior? Thanks in advance ...

Time without date
I'd like the current system time displayed in a cell when I press 'enter' Only command I know is =NOW() which displays the date and time in a cell. How do you display only the time? AM PM Doesn't matter. I've tried looking it up in help but can't but can't seem to find the answer to my question. TIA =Mod(NOW(),1) and format as hh:mm:ss -- HTH Bob Phillips "Mickey Mouse" <.> wrote in message news:428af6f8$0$5178$afc38c87@news.optusnet.com.au... > I'd like the current system time displayed in a cell when I press 'enter' &...

Entering Todays date in an excel spreadseet
This is a multi-part message in MIME format. ------=_NextPart_000_0020_01C56708.1CBA5520 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a need to automatically save the current date to a cell in an = excel spreadsheet every time the sheet is saved. Today() always returns the current date, so once I open the sheet on a = new day I can no longer see the date the information was last updated, = i.e. saved. Any help would be appreciated. ------=_NextPart_000_0020_01C56708.1CBA5520 Content-Type: text/html; charset="iso-8859-1&q...

How to erase a line drawn using line bar next to + = bar
I drew a line to separate text but now cannot get rid ot it. When I edit or move the text the line keeps reappearing even if the text is cut and pasted it moves with it, or stays with what it left See http://word.mvps.org/faqs/formatting/CantGetRidOfLine.htm. -- Stefan Blom Microsoft Word MVP "janetland" <janetland@discussions.microsoft.com> wrote in message news:0EAC6171-7D72-4664-A41F-A4D7388B213F@microsoft.com... >I drew a line to separate text but now cannot get rid ot it. When I edit or > move the text the line keeps reappearing even if the tex...

date formatting 03-22-10
I have an excel sheet which has a date in yyyy/mm/dd format saved on the internal network. other users who open the file do not see this date as the same format as the saved file. could anyone provide as to why this is happening and what can be done to correct it. thanks vandy Sounds like you have not specifically formatted the cell as yyyy/mm/dd. When you don't specifically format a date cell, Excel will look at your Windows default setting and use that, so you will see a date as yyyy/mm/dd and someone else will see it as mm/dd/yy depending on their windows setting. T...

Date input mask
I have a form that has several date input mask: 99/99/0000;0;_ . I have them auto tab to the next field. Lately after putting in all my data I copy and paste into an excel spread sheet. Then I find out that some of the dates have 208 instead of 2008. I don't know how this can happen since I have to put in 4 digits in the year. I did do some testing and once in one of the fields it did put in 208 in the year part of the year. I think I put in 01/25/2008. If I try to only put in 3 digits I get an error message and it won't let me go on. Any idea? I didn't have this pr...

Basic Exchange Front End Server Questions
Hi All, Doing a FE Exchange install for the first time and had some basic questions before I start..... Does the FE Exchange server need to be added to the domain? Does the binding order of the network cards matter? Should I bind the BackNet (internal network) to be accessed first or the FrontNet (external network). Should I take off File and Printer Sharing for MS Networks and Client for MS Networks from the FrontNet (external network)? And finally... under Advanced TCP/IP Setting of the FrontNet (external network) should I disable Register this connection's addresses in DNS and ...

how do I show filter information on a pivot chart in excel2007?
In the previous version of Excel that I used you had the option to show Pivot Chart buttons which detailled how the chart had been filter. I can't find a replacement for this in 2007 so if someone prints the chart it isn't comprehensive in that you can't tell how it's been filtered. I create a lot of these for less experienced users and this info can be critical since descisions are made based on these charts. Can anyone help please? -- ATB Russ Hi, See the reply to my question about the same issue (added 16/9). That works. You just need to modify the macro for every s...

How can I add values from several worksheets in a line chart seri.
I have an Excel document with 12 (monthly) worksheets. I want to make a line chart pulling data in one cell (same cell on each sheet) from each of the twelve sheets. In other words, Jan F2 + Feb F2 + Mar F2, etc. Can I do that on the chart's Source Data menu in the Series/VALUES field? If so, what is the proper language/formula. If not, what are my options? Do I have to make a total page and pull from it? Also, is it possible to make the Category (X) axis label be the Worksheet Name (Jan, Feb, Mar) Lastly, is there a place that gives sample formulas for someone trying to brush...

[Entourage 2004] Show me the date!
Hi, How can I force Entourage 2004 (SP2) to sort mails by received and at the same time show me the receive date instead of weekday and time? It doesn't work to disable 'Use relative dates in lists (Today, Yesterday)' in General Preferences > General. It still shows me weekday and time instead of the date. -- Regards Madsen Under the "View" menu, set "Preview Pane" to "Below List" or "None". If the Preview Pane is set so that it is "On Right", the message list will always use relative dates. I agree that it is an unfo...

Everytime I email a spredsheet, the receiving end cannot open it
The person on the receiving end cannot open the spread because it is encrypted. I just upgrade to office 2007. I do I turn this feature off? Two known solutions: 1) Save the WB as "97-2003" (xls) instead of XLSX. 2) Ask the recipient(s) to install the Compatibility Pack: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=941b3470-3ae9-4aee-8f43-c6bb74cd1466 Micky "mj61274" wrote: > The person on the receiving end cannot open the spread because it is > encrypted. I just upgrade to office 2007. I do I turn this feature off? ...

overcoming zero values in line graph
I have a 12-month line graph where some of the earliest and most recent data is still zero. Is there a way to have Excel not plot zero values - to avoid the big rises and drop offs they cause - and, rather, just have the graph start and end wherever there is positive data? (NOTE: for multiple reasons, I can't erase the formulae in the cells yielding the zero values, or have them display the N/A message....) Thanks!!! Marika :) There is no reason why you cannot have a 2nd data set that has the N/A values. Plot this second set and use the first for whatever you are using it ...

Chart's DataTable
Some times we need to show source data with chart. we could turn it true by use specific name in axis(xlcategory) or by using source data 's linking picture . Both of these could help us to show specific series. and as all of us know that, the chart's datatable will show all of the series' value. I want to know whether we can hide any dummy series in chart's data table directly. Thanks for your answer in advance. Hi, No. Formatting, including hiding, of chart data table elements is very restricted. This may help http://tushar-mehta.com/excel/newsgroups/data_table/index.htm...

How to have a date range equal a particular month
What is the expression for converting multiple dates mm/dd/yyyy to the coinsiding month? It is according to how you are going to use the data but try this -- Format([YourField], "yyyymm") This will give you 200711 for this month. Format([YourField], "mmmm yyyy") This will give you November 2007 for this month. -- KARL DEWEY Build a little - Test a little "tvillare" wrote: > What is the expression for converting multiple dates mm/dd/yyyy to the > coinsiding month? Format(DateField, "yyyy-mm") as stri...

Creating a calculated field using dates in a form
I am trying to use dates from 2 fields in a form (HireDate-DOB) to calculate age. Then, using the age, I want to display a message if the age is less than 18. I think I have written a conditional statement correctly, but am unsure where to place it so that the message displays when the condition is met. I am totally new to Access 2007 and VB. You did not say what the content of your 'conditional statement' was and how you plan on using it. What has HireDate to do with it? Is it you want to see if their age is less than 18 on the date of hiring? Then this will c...

How do I remove multiple line feed(s) or carriage return(s)?
I have carriage returns in a column as follows: address1 CR city CR state CR zip --and-- address1 CR address2 CR city CR state CR zip I need the city, state and zip to end up in the same column. Can you help? Thanks. sra Excel 2003 -- You mean you want the city in its own column, the state in its own column and the zip in its own column? And your data always has 3 or 4 CR in the cell? If yes, then (assuming the data is in A1:Axxx), then put this in B1: =IF(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))=3,"dummystring"&CHAR(10),"")&A1 (and ...

wrap text in a chart data table #2
Hi, Im have created a graph, which ive had no problem, but i when i click the option to "show data table" i cannot get the titles to wrap to the box, i have searched but i cannot find an option to wrap the text as i need it for a presentation. I do not want to just simply alter the font size if possible. Is there anyway of doing this? ...

Count cells in a column that contain dates
Column J contains dates in some cells, and text in others. I need to count the number of cells in this one column that contains dates. How can I do this? If the only cells that are in that column that are numeric (like dates), you can use: =count(a:a) (Dates are just numbers formatted nicely in excel.) Cachod1 wrote: > > Column J contains dates in some cells, and text in others. I need to count > the number of cells in this one column that contains dates. How can I do > this? -- Dave Peterson ...

Chart line colors
Hi I create a few charts in code from VB.NET using the excel interops, office 2003. In my software I need to be able to set the color of the lines i.e. the color of each series on the chart. There may be up to 9 series per chart. How do I asign a color to the series lines on the chart? Thanks Pieter Get out your version of Excel, make a chart like the one your code will produce, then record a VBA macro while you change the series formats manually. Then all that's left is to translate the VBA into VB.Net. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solut...

Chart background color not copying along
Hi, I have a problem with charts that are placed on seperate chartsheets within my workbook. The chart-area is filled in different of yellow with a fill-effect. No problems thus far. But now when I'm copying the chart to word, the background-fill is reset to a black/white scheme instead of yellow. Strange behaviour that didn't occur up till november 2008. I've been researching this for a while. Up till now I found the following: This only happens when the chart is on a seperate sheet, not when it is positioned on a worksheet. It only happens with fill-effects. The color-swit...

iFrame and dates
Hi Guys, I'm trying to fill in a crmForm that has a date type field, from an iFrame. In the codebehind of the iFrame's aspx I'm using "parent.crmForm.all.<fieldname>.value=data where data is a CrmDateTime value, but the field does not appear. Please help, Thanks, Paulo Fonseca ...

Move to end of edit
I have an edit box in a grid edit dialog that opens when I type a single character. if(nChar > 0) dlgGrid.m_strEditCell = (TCHAR)nChar; The character is selected but I want to deselect it and position the cursor after the character. How do I do that? -- Steve Wolstenholme Neural Planner Software EasyNN-plus. The easy way to build neural networks. http://www.easynn.com On Sun, 18 Jun 2006 16:04:21 +0100, steve@tropheus.demon.co.uk wrote: >I have an edit box in a grid edit dialog that opens when I type a >single character. > >if(nChar > 0) > dlgGrid.m_strEd...

Buying a mutual fund with a 5.75% front end load.
Nothing I'm doing seems to be giving me sensible answers. I assumed I'd take the purchase price time 5.75% and put that in commission. Or, do I jack up the purchase price by 5.75% and then take that amount back out in commission? Thanks in advance, Harold Morgan In microsoft.public.money, Harold Morgan wrote: >Nothing I'm doing seems to be giving me sensible answers. I assumed I'd take >the purchase price time 5.75% and put that in commission. Or, do I jack up >the purchase price by 5.75% and then take that amount back out in >commission? I would enter the numb...