Date axis labeling algorithm for Excel

Question

I routinely graph time series data in Excel, and I find the default Excel date-axis labeling on line graphs to be very ugly. In particular,

1) Dates are often rotated horizontally or vertically, which compresses plot area real estate and makes the date labels harder to read. This is because there are two many major tick marks.

2) Labels show too much precision: time series databases will typically dump something like a monthly time series into the spreadsheet with date values like 1/1/2001, 2/1/2001, etc. Excel will try to show the full mm/dd/yyyy date string by default, which might be preferable for a short daily time series but is undesirable for a non-daily series or a series that runs over a long time period. In these cases, formatting like mm/yyyy or yyyy might be preferable.

My question is, does anyone have a good algorithm already written that can make a pretty date label axis? Horizontal labels only with ticks spaced far enough to accommodate that, and intelligent formatting of the date as the data starts to cover a longer and longer time span (i.e. labels that transform from 1/1/2001 to 1/2001 to 2001). I will ultimately have to write this using VBA, but I could translate an algorithm written in another language.

Thanks.

Answer 1

This is not a true answer, but maybe a fertilizer .... I share your concerns, as I often run into the same kind of troubles.

Excel (2003) does not support charts with a true date axis (where the distance from 01-Feb to 01-Mar is 28 units and 01-Mar to 01-Apr is 31 units). The best approximation I found (for charts spanning more than 2-3 months) is to

• use an X/Y chart
• manually set the Minimum and Maximum of the Value (X) scale to the integer representing the first of a month (e.g. 01-Feb-2010 represented by integer 40210)
• set major unit to 30,42 (average length of a month - Excel can't do variable major ticks)
• set minor unit to 7 (so I get weeks clear at least)
• use a custom number format (e.g. "MMM-YYYY") on the X axis

Sometimes I tweak the above by temporarily displaying the date as "DD-MM-YYYY" and look how many first-days-of-a-month I hit with my scale, and adjust the Minimum to get an optimum. The longer the date range the less accurate it gets.

Hope that helps at least a little ....