Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Template workbook forgets dates for x axis - reverts to 01/01/1900
I have set up a workbook with the design that data is input (pasted) in 8
columns: date, time, and 6 x 'data point' columns. A calculation sheet does some work on the data and charts on different sheets look at the calculation sheet for data. The workbook is supposed to be a master with no data and the charts looking at blank cells until data is input. Then a copy is saved for ongoing use with all charts set up and displaying the correct info without user interaction. The calculation sheet combines the input date and time to display "dd/mm/yyyy hh:mm"(with a formula that leaves the cell bland if there is no input) - this makes up the x-values. The graph works fine when I do it by hand and get the master ready, but when I reopen it and add new raw data the graph plots with everything starting from 01/01/1900. To fix the problem I have to re-select the data for x values for each series and they jump to the correct date (even though no input data changes?) I have tried inserting a column and leaving the excel date (i.e. 39950.243) and plotting against that but amazingly it still suffers the same loss of memory and plots between 0 and 300? Is it something to do with not liking formulas?? I really want to avoid having to make a macro replot all the graphs as my chart macro skills are lacking... |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Template workbook forgets dates for x axis - reverts to 01/01/1900
How do you define a blank? If you use "", it is not interpreted as blank
but as a text value. On the X axis of an XY chart, this will cause the whole set of dates to be interpreted as text values, which have no numerical values, and Excel will default to numbers like 1, 2, 3, etc. for X values. One on the axis will be formatted as 1/1/1900 (the first day in Excel's date system is 1/1/1900). Instead of dumping "" into a cell to make it look blank to us humans, use NA() which makes it almost blank to an Excel XY or line chart. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Al" wrote in message ... I have set up a workbook with the design that data is input (pasted) in 8 columns: date, time, and 6 x 'data point' columns. A calculation sheet does some work on the data and charts on different sheets look at the calculation sheet for data. The workbook is supposed to be a master with no data and the charts looking at blank cells until data is input. Then a copy is saved for ongoing use with all charts set up and displaying the correct info without user interaction. The calculation sheet combines the input date and time to display "dd/mm/yyyy hh:mm"(with a formula that leaves the cell bland if there is no input) - this makes up the x-values. The graph works fine when I do it by hand and get the master ready, but when I reopen it and add new raw data the graph plots with everything starting from 01/01/1900. To fix the problem I have to re-select the data for x values for each series and they jump to the correct date (even though no input data changes?) I have tried inserting a column and leaving the excel date (i.e. 39950.243) and plotting against that but amazingly it still suffers the same loss of memory and plots between 0 and 300? Is it something to do with not liking formulas?? I really want to avoid having to make a macro replot all the graphs as my chart macro skills are lacking... |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Template workbook forgets dates for x axis - reverts to 01/01/
Jon,
That has worked! Once again saved me. Cheers mate. "Jon Peltier" wrote: How do you define a blank? If you use "", it is not interpreted as blank but as a text value. On the X axis of an XY chart, this will cause the whole set of dates to be interpreted as text values, which have no numerical values, and Excel will default to numbers like 1, 2, 3, etc. for X values. One on the axis will be formatted as 1/1/1900 (the first day in Excel's date system is 1/1/1900). Instead of dumping "" into a cell to make it look blank to us humans, use NA() which makes it almost blank to an Excel XY or line chart. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Al" wrote in message ... I have set up a workbook with the design that data is input (pasted) in 8 columns: date, time, and 6 x 'data point' columns. A calculation sheet does some work on the data and charts on different sheets look at the calculation sheet for data. The workbook is supposed to be a master with no data and the charts looking at blank cells until data is input. Then a copy is saved for ongoing use with all charts set up and displaying the correct info without user interaction. The calculation sheet combines the input date and time to display "dd/mm/yyyy hh:mm"(with a formula that leaves the cell bland if there is no input) - this makes up the x-values. The graph works fine when I do it by hand and get the master ready, but when I reopen it and add new raw data the graph plots with everything starting from 01/01/1900. To fix the problem I have to re-select the data for x values for each series and they jump to the correct date (even though no input data changes?) I have tried inserting a column and leaving the excel date (i.e. 39950.243) and plotting against that but amazingly it still suffers the same loss of memory and plots between 0 and 300? Is it something to do with not liking formulas?? I really want to avoid having to make a macro replot all the graphs as my chart macro skills are lacking... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
X Y Scatter Chart - Dates on X axis starting at 1900 | Charts and Charting in Excel | |||
Dates before 1900 | Excel Worksheet Functions | |||
Dates Before 1900 | Excel Worksheet Functions | |||
Pre-1900 dates | Excel Discussion (Misc queries) | |||
Dates before 1900 | Excel Worksheet Functions |