Home |
Search |
Today's Posts |
#1
|
|||
|
|||
dates start at 01 January 1900
Hi. I'm trying to create a line chart with the x (category) axis showing time. If I open Chart Options and select Automatic for the x axis, the dates start at 01 January 1900, ignoring my date data. But if I choose Time-scale (in Chart Options), the x axis is O.K. (it uses my values), but all my Y values vanish completely, so that the chart is blank. Any ideas would be appreciated. :( Thanks. -- melo ------------------------------------------------------------------------ melo's Profile: http://www.excelforum.com/member.php...o&userid=26784 View this thread: http://www.excelforum.com/showthread...hreadid=400394 |
#2
|
|||
|
|||
have you tried setting the minimum value on the scale to be the date
you want, with an automatic axis? |
#3
|
|||
|
|||
What dates are you using? 01 January 1900 is how Excel formats the
number 1 as a date. If you are not specifying true numerical dates, Excel may be simply counting 1, 2, 3,... I suspect Excel thinks your dates are just text labels without any numerical value. I also think the behavior you're getting when you say Auto is selected, where the dates appear starting with 1/1/1900, is what happens when you've actually selected TimeScale. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ melo wrote: Hi. I'm trying to create a line chart with the x (category) axis showing time. If I open Chart Options and select Automatic for the x axis, the dates start at 01 January 1900, ignoring my date data. But if I choose Time-scale (in Chart Options), the x axis is O.K. (it uses my values), but all my Y values vanish completely, so that the chart is blank. Any ideas would be appreciated. :( Thanks. |
#4
|
|||
|
|||
Philcud: I did initially try playing around with minimum values, but now, I can't seem to be able to locate that feature anymore! Any hints? Jon: the dates I'm using range from 01 September 2004 to 30 August 2005 (weekdays only, so that there'd be approximately 250 values). I am specifying Date (on the Format Axis [box] Number [tab] ). When I select Auto (from Chart Options Axes), my Y values (i.e. lines on the plot area) are O.K., but the dates begin from 1/1/1900. When I select Time-scale, my own dates (i.e. starting 1/9/2004) finally come up on the x-axis, but all the Y-values disappear. There's nothing left on the plot area. -- melo ------------------------------------------------------------------------ melo's Profile: http://www.excelforum.com/member.php...o&userid=26784 View this thread: http://www.excelforum.com/showthread...hreadid=400394 |
#5
|
|||
|
|||
Are the dates left aligned in the cells, or right aligned? (Make the
cells wide enough to distinguish, and format the column to have General horizontal alignment.) How are the cells number formatted? I think Excel is treating them as text. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ melo wrote: Philcud: I did initially try playing around with minimum values, but now, I can't seem to be able to locate that feature anymore! Any hints? Jon: the dates I'm using range from 01 September 2004 to 30 August 2005 (weekdays only, so that there'd be approximately 250 values). I am specifying Date (on the Format Axis [box] Number [tab] ). When I select Auto (from Chart Options Axes), my Y values (i.e. lines on the plot area) are O.K., but the dates begin f rom111900. When I select Time-scale, my own dates (i.e. starting 1/9/2004) finally come up on the x-axis, but all the Y-values disappear. There's nothing left on the plot area. |
#6
|
|||
|
|||
Use an "XY (Scatter)" chart.
In a Line chart, the (optional) x-values (if given) are considered category labels. When numeric treatment is needed (as in fitting a trendline, or apparently a date format) then the numeric values are taken to be 1,2,3,... (which as dates are 1Jan1900, 2Jan1900, ...). Jerry melo wrote: Philcud: I did initially try playing around with minimum values, but now, I can't seem to be able to locate that feature anymore! Any hints? Jon: the dates I'm using range from 01 September 2004 to 30 August 2005 (weekdays only, so that there'd be approximately 250 values). I am specifying Date (on the Format Axis [box] Number [tab] ). When I select Auto (from Chart Options Axes), my Y values (i.e. lines on the plot area) are O.K., but the dates begin from 1/1/1900. When I select Time-scale, my own dates (i.e. starting 1/9/2004) finally come up on the x-axis, but all the Y-values disappear. There's nothing left on the plot area. |
#7
|
|||
|
|||
Hi. Finally, after a lot of poking around, I found a fix. I'm not sure if this is any news to anyone, but just in case there's someone out there with the same problem, I'll describe what I did. i) right click on the plot area and select Source Data; ii) go down to 'Category (x) axis labels' and click the Collapse Dialog button (at the right end of the blank slot); iii) select the column or row in your worksheet containing the date data that you want displayed along the x-axis of your chart; iv) click the Collapse Dialog button. That should be it! Many thanks to Philcud, Jon and Jerry for your help. -- melo ------------------------------------------------------------------------ melo's Profile: http://www.excelforum.com/member.php...o&userid=26784 View this thread: http://www.excelforum.com/showthread...hreadid=400394 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates earlier than 1900 | Excel Discussion (Misc queries) | |||
Find project start and end dates in a DB with many different proje | Excel Worksheet Functions | |||
Where is DateDiff function in Excel 2002 ? | Excel Worksheet Functions | |||
Need to sort dates before 1900 in proper order | Excel Discussion (Misc queries) | |||
Inserting Blank Rows Macro? | Excel Worksheet Functions |