Excel charts - using Dates (specifically quarters of the year)

[redheadclay]

Hey, this is my first post. I am a long time Excel user, first time MrExcel, though. Here is my question:

I have been trying several ways just to show the quarters of the year on a chart with the format "Q#-" yy

How can I show this on the timescale of a graph? So far, I have set up custom date settings of "Q1-" yy for the first quarter dates, and "Q2-"yy for the second quarter dates, and so on. It looks nice in the spreadsheet, but when I chart it, the dates all show Q1. I think this may be because the excel chart takes the first cell's formatting and extends it to all data sets.

In the end, all I am trying to do is show a quarterly format at the bottom of my chart. Any suggestions?? Thanks

 

[Andrew Poulsom]

Welcome to the Board.

Can't you just construct a formula to return the quarter and use that column as the axis labels? Example:

="Q"&ROUNDUP(MONTH(A2)/3,0)&"-"&RIGHT(YEAR(A2),2)

 

[redheadclay]

I would like to, but those options are greyed out. (Thanks for the quick reply!)

 

[Andrew Poulsom]

What options are greyed out? Can't you put that formula in a spare column?

 

[redheadclay]

I added the formula to the spreadsheet (which worked wonderful). But when I select the chart, then Chart>Chart Options>Axes, my options to select time-scale is greyed out

 

[Andrew Poulsom]

Assuming you have entered the formulas, right click the Chart and choose Source Data. On the Series tab change the Category (X) axis labels reference so that it refers to the column with the formulas. Then click OK.

 

[redheadclay]

When I do this, I get the scale 0 -35 at the bottom, with no mention of dates. When I try to go to edit the chart options, and select axes for time-scale it is greyed out.


(again, thanks for your help)

It is a simple sheet, I am happy to email it to you...

 

[redheadclay]

I am using a dual-axis chart (if that makes any difference), and there simply isn't any option to label the "category x axis label" in my source data sheet

 

[redheadclay]

OK- I think I figured it out. I was using the XY chart type, which then locks out the option to label the x coordinates. Problem solved when I move to a typical line type...Thanks!