End of Month Chart Axis Labels

**djt76010** · 10-16-2012, 07:02 PM

I am creating a monthly line graph in Excel 2007 and even though the "Horizontal Axis Labels" points to a column in my data that contains end of month entries for each month, the axis labels are all showing up as beginning of month. It does look like it is adjusting the chart lines accordingly to match the dates, but I can't figure out why it is changing to beginning of month labels when my data says otherwise. Any thoughts?

**jeffreybrown** · 10-16-2012, 07:15 PM

At least for me it would be nice to see the example workbook...

**djt76010** · 10-16-2012, 07:37 PM

Here is an example

End of Month Chart.xlsx

**JosephP** · 10-17-2012, 02:58 AM

if you have data for every month end you can simply change the axis to a text axis rather than date axis

**djt76010** · 10-17-2012, 01:56 PM

That does work in this case, but is there a way to keep the date axis? There are other charts we will do in the future that don't have entries for each month that we would like the gap to represent missing months.

Thanks for your help

**JosephP** · 10-17-2012, 02:53 PM

unless you can create entries for the missing months you would have to use a dummy series plotted as xy scatter to plot points (with no marker showing) and use the data labels as axis markers

**djt76010** · 10-17-2012, 03:45 PM

Is there any way to get the data axis option to work? We are dealing with graphs that multiple people will be modifying for various purposes (ie : displaying different date ranges) and as the person fielding questions, it would definitely make my life easier if the date options were functioning as expected.

**JosephP** · 10-17-2012, 04:10 PM

not that I know of-there isn't a simple fix

**Andy Pope** · 10-18-2012, 05:53 AM

You can use a dynamic named range to plot a xy-scatter on the secondary axis and use the data labels from that to plot end of month labels

named range
EOMLabel: =DATE(YEAR(MIN('Chart Data'!$B$2:$B$38))+1,ROW('Chart Data'!$A$1:INDEX('Chart Data'!$A:$A,DATEDIF(MIN('Chart Data'!$B$2:$B$38),MAX('Chart Data'!$B$2:$B$38),"m")+1,1)),0)

EOMData: =EOMLabel*0

I've left the data markers and secondary axis un formatted so you can see what's going on.