Excel 07 - Line Chart with Month-end dates

[Lori]

Having difficulties creating a line chart using daily balances, but I would
like the x-axis to show the month-end date. My start date is 12/31/2007 and
my end date is 6/30/2008. I have daily information that I would like
plotted, however only the month-end date (1/31/2008, 2/28/2008, etc) to show.


I have experimented with the axis options (day, month, year), however it
seems to default to the 1st day of the month. If using every 30 days, it
also becomes off.

Please help. I'm using an existing chart from Excel 03, and it will not
convert properly...I've also tried creating a new chart from scratch.

Thanks,
Lori

 

[Jon Peltier]

This works just fine in 2003. If you use a base unit of days, a major unit
of 1 month, and a minimum scale value of the last day of a month, then the
axis has a tick at the last day of each month.

In 2007 it doesn't work.

- Jon

 

[Lori]

So, this means that using month-end dates on a chart can not be done in 2007?
I have added month-end columns and graphed it only using points. But the
problem is still the axis labels.

 

[Jon Peltier]

As always, you can fake it using a helper series. Hide the default X axis
labels. Create a new series using dates from the end of each month for X,
and zero (or the minimum of the Y axis) for Y. Change the series to an XY
series on the primary axis, hide it (no markers, no lines) or use a small
black cross as the marker, apply data labels using the X value option, and
locate the labels below the axis.

- Jon