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

redheadclay

New Member
Joined
Sep 24, 2008
Messages
15
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Sep 24, 2008
Messages
15
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Sep 24, 2008
Messages
15
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

New Member
Joined
Sep 24, 2008
Messages
15
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

New Member
Joined
Sep 24, 2008
Messages
15
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,359
Messages
5,444,005
Members
405,259
Latest member
charliechaz

This Week's Hot Topics

Top