Plotting dates

StuLux

Well-known Member
Joined
Sep 14, 2005
Messages
682
Office Version
  1. 365
Platform
  1. Windows
I have some date data to be plotted. The data comes from various calculations and lookkups and some fo the cells will be blank on some occasions. When the cell is blank I don't want to plot the point (as Excel will, by default, presume this is a date of 00-Jan-00). Is there either a formula I can use to put something else in these cells so they are not plotted or is it something I can do within the chart formatting functions?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
That is better as they are no longer treated as dates but in the chart I am trying to format I have data lables and these now show as #N/A - is there any way of avoiding this - many thanks for the help.
 
Upvote 0
Again, many thanks. I have the XY chart labeller add-in but this doesn't solve the problem as I would have to set the labels manually each time the data updates.
It seems to me that what I need is a formula that produces a "completely blank" cell if certain conditions aren't met but I don't think Excel can do this.
 
Upvote 0
Say the data to be plotted is in the range B1:B10. In C1 enter:

=IF(ISNA(B1),"",B1)

and copy down. Then use C1:C10 as the range for the data labels.
 
Upvote 0
This takes us back to square 1 because Excel is plotting the "" as 00-Jan-00 - or have I missed something here. I've pasted (hopefully!) the sheet I am basing the chart on - the top table is from your 1st suggestion and the 2nd table based on your latest suggestion, hope this helps and thanks.
 
Upvote 0
Excellent - and so simple now you've clarified - many thanks, I'm sure I'll be back with more queries in the near future!
 
Upvote 0

Forum statistics

Threads
1,226,728
Messages
6,192,691
Members
453,747
Latest member
tylerhyatt04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top