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

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.

----
Regards,
John Mansfield
http://www.pdbook.com


"nanoking" wrote:

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

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

"John Mansfield" wrote:

> 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.
>
> ----
> Regards,
> John Mansfield
> http://www.pdbook.com
>
>
> "nanoking" wrote:
>
> > 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.

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/
_______

nanoking wrote:

> 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
>
> "John Mansfield" wrote:
>
>
>>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.
>>
>>----
>>Regards,
>>John Mansfield
>>http://www.pdbook.com
>>
>>
>>"nanoking" wrote:
>>
>>
>>>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.