How do I eliminate non-used dates (weekends) from a chart?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My data uses only week day dates. The chart that results from it is adding
weekend dates even though they don't appear in the range for the X axis. It
leaves gaps in the chart as a result.
 
Nanoking,

One option is to reference your X-axis series with a formula that converts
the date to text but still leaves the "text" date looking like a date. For
example, assuming the data below starts in cell A1, convert column A to text
by using this formula:

=TEXT(A2,"mm/dd/yy")

col A col B col C
Orig. Date Text Date Data
01/01/04 01/01/04 5
01/02/04 01/02/04 4
01/03/04 01/03/04 5

In the formula view, column B looks like

=TEXT(A2,"mm/dd/yy")
=TEXT(A3,"mm/dd/yy")
=TEXT(A4,"mm/dd/yy")

Change your X-axis reference from column A to column B. The chart will read
the X-axis dates as text and will not leave gaps.
 
Thanks, John. That's what I'm doing. I was hoping for a more elegant fix.
I had hoped there was a formatting element I was overlooking in either the
chart or the data. As it is, I still want to have the date form and so use
two columns; one formatted for text.

I appreciate your response. Perhaps in the next edition MS will recognize a
need for the creation of a workday calendar. Something like MS Project would
be nice with a basic setting that eliminates weekends. That way you could
also fill a column and not have weekend dates included.

Cheers!

JDL
 
Probably it's quicker to go to Chart Options on the Chart menu, click on
the Axes tab, and change from the Time Scale (or Automatic) option to
Category under the Category X Axis.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top