Template workbook forgets dates for x axis - reverts to 01/01/1900

[Al]

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...

 

[Jon Peltier]

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-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

 

[Al]

Jon,

That has worked! Once again saved me.

Cheers mate.