Cannot Exclude weekends from XValues

Hey,

Lets say I have two dates,

1. 8/3/2009
2. 8/10/2009

I have a function which goes through each date, and calls Weekday(currDate) to check if it is a weekend(ie. 1 or 7). I only write weekday dates to a row in my excel sheet. This works fine.

However, when I create a range out of these dates and assign it to XValues

Please Login or Register  to view this content.

The chart displays the weekend dates!

I created a simple loop, and put a breakpoint to check the dates the range:

Please Login or Register  to view this content.

The range contained:
8/3/2009
8/4/2009
8/5/2009
8/6/2009
8/7/2009
8/10/2009


I have no idea why the chart includes weekends,
8/8/2009
8/9/2009


Any ideas???

Hi, sounds as if your x axis is a time scale axis. You need to edit the Chart Options to set the x axis to Category instead.

A time scale axis can be used when you want to plot data points on the true representation of when in time they occurred, relative to each other, so Excel inserts missing dates on the axis. This can have its uses.

But if you really only want to plot the x markers shown in your data, you need to select the Category option for you x axis.

If you use an XY Scatter chart with the dates on the X axis, it will be treated like a date/time scale axis and missing dates will be interpolated. Change the chart type to a line chart instead and select the Category for the x axis to plot only the data points in your table.

ohhh, impressive. I only do charts on foot, not with VBA (waaaayy over my head), so I don't have a clue how to do that programatically. Sorry.

I'm sure the VBA wizards here can help you. Your best bet would be Andy Pope. Check out his site and maybe also Jon Peltiers www.peltiertech.com, coz he does a lot with VBA and charts, too.

Add the following line into your code

Please Login or Register  to view this content.