Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Does anyone know an easy way to format dates by quarter for use in chart
axes? I am plotting charts of daily data over 5+ years, and I can't figure a
way to get the chart marks to hit on the same day of each year or by quarter.
Since quarters aren't a date format, there's no especially easy way that
I know of.
One workaround (XY-Scatter Chart) if you can live with 08Jan, 08Apr,
etc):
1) Create an XY-Scatter chart of your data
2) Format the X-Axis:
Scale:
Major unit: 91.25
Number:
Custom: yymmm
Another Workaround (Line Chart) if you can live with 08Jan, 08Apr, etc):
1) Create a line chart of your data
2) Format the X-Axis:
Scale:
Base unit: Days
Major unit: 3 Months
Number:
Custom: yymmm
Another workaround (Line Chart):
Assume your dates are in A2:A100 and your Values are in B2:B100.
1) Insert a column in-between (i.e, B:B, moving data to B2:B100)
2) In B2 enter:
=TEXT(A2,"yyyyQ")&INT((MONTH(A2)-1)/3+1)
3) In B3 enter:
=IF(COUNTIF($B$2:B2, TEXT(A3,"yyyyQ")&INT((MONTH(A3)-1)/3+1)), "",
TEXT(A3,"yyyyQ")&INT((MONTH(A3)-1)/3+1))
4) Copy B3 down to B100.
Create a line chart from B2:C100, formatting the X axis scale interval
between labels to 1.
Note that this will place the labels where the quarter changes, not
necessarily proportional to the date intervals.
