How can I set month/quarter/annual date intervals

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have data collected on a daily basis (for work days) over several years,
and I want to plot it on an X-Y plot versus date. I would like the monthly
intervals showing on the scale to be like 1/1/04, 2/1/04, 3/1/04, etc.
Similarly, for annual intervals, 1/1/03, 1/1/04, 1/1/05, etc. Excel just
wants to put in the same number of days for the intervals. Any suggestions?
 
It sounds like you are using an XY chart.

For XY trend charts, I often run into the issue you are talking about
frequently. I use a few formatting tricks:

For my X Axis number format I double click the X axis to get Format Axis
window. I select number tab and enter my date format. For monthly data
spanning years, I use "mmm'yy" which will show up as Mar'06 on the chart X
axis.

For my interval, I use 32 days to make sure that I get my months to
increment.

I have several trend chart examples at
http://processtrends.com/pg_charts_trend_chart.htm

...Kelly

(e-mail address removed)
 
Thanks, Kelly, but this did not work for me. When I used 32 days, after about
every 20 months, it skipped a month on the axis, as if it were "rounding" to
the nearest month.

I have an old chart that has on the x-axis, 1/4/99, 2/4/99, 3/4/99, etc
through 8/4/04. Unfortunately all I have is a printed copy and have lost the
Excel file from which it was printed. So I know it can be done, but I just
can't figure it out.
 
John:

How many months of data do you have to chart?

Have you tried 2 month interval (63 days)?
What about quarter (94 days)?


...Kelly
 
Kelly,
It varies. Sometimes I have 5 years worth, so that would be 60 months. Since
months, quarters, and years are of different lengths, having a certain number
of days as the major unit on the scale doesn't work out. Is there a way to
tell Excel to have as the major unit on the scale months or quarters or
years, even though the data are plotted by days? This is a format axis issue,
and I have done it before, but don't remember how.
John
 
Kelly,
Success! Whoopee!
I finally figured it out. Instead of an X-Y chart, use a Line chart. Then I
can have the major units as months.
John
 
Back
Top