View Single Post
  #1   Report Post  
Old December 13th 04, 09:10 PM
Richard H
 
Posts: n/a
Default Date format collapses diagram

I have a strange problem which is different to explain.

I have a workbook with five sheets containing data and a cell with
a date in it. A sixth sheet contains a table with data collected
from the first five sheets, with the dates as categories, like
this;

08.2004 09.2004 10.2004 11.2004 12.2004
3 4 4 5 3
4 3 5 4 4
3 2 5 3 4


The table is used for generating a line diagram. Now, the diagram
looks fine when I only include data and categories from the first
four columns. If I include the last column, the diagram collapses
to one set of data points on top of each other, with a category
label of 38292 or something like it.

If I delete the last category (12.2004), the error goes away and a
proper line reappears. But then I lose the category label in the
diagram.

The error only appears when the category cells are formatted as
dates. If I format them as text, the line diagram is OK. The
trouble is that the five data sheets contain data which is
supposed to be imported via a macro, and the dates import in date
format, even if I've preformatted the cells they're copied into as
text. With some combinations of cell formats texts values (like
38292) appear in the diagram instead of dates.

I can't find a way to have Excel keep the dates in text format
when importing. And I can't for the life of me figure out why the
problem only appears when the last one of the five dates is added.

Does anybody understand what I mean here ...? And - please -
suggest a way to get around this??