Graph using date stamps with empty cells

[AnnieMcken]

I have a data file of temperature reading with date stamps, and I need to plot various graphs where the indexes from 1h00 to 5h00 are removed. To this point, everything works fine, using a formula to identify the undesirable date stamps and replace by a void.

Please Login or Register  to view this content.

The resulting column is exactly what I need, and plotting a long range of it, with the associated temperature reading should result in a few curves separated by 4-hours gaps...

The thing is that I could not find a way to properly set a void. Using "" in the formula is treated as a zero, and thus it reads "jan 1, 1900". Only manually deleting the "content" of the voided cells allows me to get the right graph. Considering +50k lines of data, I would like to avoid doing that!

I included a sample file with 3 graphs; the first one uses only data with dates, so the plot is just fine. The second graphs shows a plot where the void cells have been manually "cleaned" with delete, and still shows an acceptable result (but can't do with the hole file!)
When unchanged, using void cells as obtained with the formula in column C, the graph is all wrong, using the 0 date index (jan 1 1900).

TEST.xls

We tried both in Excel 2003 and 2010 with the same results.

Ideas anyone?

Thanks,

Annie

[AnnieMcken]

I managed with a more thorough search of existing threads in the forum :P

SOURCE: line chart with some #NA cells

Izandol wrote:

If it is one-time operation, because your formula cells return error value, you may select the column of formulas, press f5, then select "Special..." button and choose 'Formulas' and only "Errors" from option list, then select OK. This will select all error cells and you may press Delete to clear them.

So worked for me as well. After copy/paste the relevant column, void values returned an error, and this worked perfectly. I can now use my data sheet for my graphs!