Date axis marking algorithm for Excel

Question

I usually collect time series data in Excel, and I find that marking by date of the default date on line charts is very ugly. In particular,

1) Dates often rotate horizontally or vertically, which compresses plot real estate and makes date labels harder to read. This is due to the fact that there are two large marks.

2) Shortcuts show too high accuracy: time series databases usually dump something like a monthly time series into a table with date values ​​such as 1/1/2001, 2/1/2001, etc. Excel will try to show the default full string length mm / dd / yyyy, which may be preferable for short daily time series, but not desirable for a series that is not related to daily periods, or a series that runs for a long period of time. In these cases, formatting may be preferable, for example mm / yyyy or yyyy.

My question is: does anyone have a good algorithm already written that can make a pretty axis of date stamps? Horizontal labels with only marks far enough to fit this, and intelligent formatting of the date when the data begins to span a longer and longer period of time (i.e. Labels that convert from 1/1/2001 to 1/2001 to 2001 year). I will eventually have to write this using VBA, but I could translate an algorithm written in another language.

Thanks.

Answer 1

This is not a real answer, but perhaps fertilizer ... I share your problems, as I often face the same problems.

Excel (2003) does not support charts with a true date axis (where the distance from February 1 to March 1 is 28 units, and from March 1 to April 1 it is 31 units). The best approximation I found (for charts spanning more than 2-3 months) is

• use chart x / y
• manually set the minimum and maximum scale of values ​​(X) to an integer representing the first of the month (for example, 01-Feb-2010, represented by the integer 40210).
• set the base unit to 30.42 (average month length - Excel cannot make variables main ticks).
• set the lowest unit to 7 (so that I get weeks at least)
• use the user number format (for example, "MMM-YYYY") on the x-axis

Sometimes I adjust the above by temporarily displaying the date as “DD-MM-YYYY” and looking at how many first days of the year I hit with my weight, and set Minimum to get the best. The longer the date range, the less accurate it is.

Hope that helps at least a little ....