How to create diagram in spreadsheet with dates on x-axis?

How do I make a line chart with dates in a spread sheet, where the distance between each data point on the x-axis is correct according to the number of days between each measurement?

In Excel:

Chart > Options >Axes > Main Axis (X) > Time scale.

Here's my advice for doing what you want in Excel:

• Change all of the dates to a format that Excel will recognize, like "13-oct-09." Make sure to include the year, so that it doesn't assume that they're all 2010.

• Remove the units ("g" and "cm") from the data cells so that Excel can treat them as numbers. Either put the units into the labels for each data stream or put them in the Y-axis labels (more on those later).

• Where there are dates in which one data stream contains data and the others don't, put values in the ones that don't have data - either the same as the last value or an interpolated value between the last one and the next one. I'm not sure how else to keep the graph from thinking that these are 0-value points.

• To create your chart:

  • Select Insert / Chart.

  • Select the chart type of X-Y Scatter, and the chart sub-type with dots connected by curvy lines. This will make the X spacing of the points be scaled by the distances as you desire and will connect the points with a smoothed line to make it easy to see trends.

  • Under Source Data, select the Series tab, then add a series for each data stream. For each series, the Name should point to your label for the row (e.g. "Tinus weight"), the X-Values should point to the row of dates, and the Y-values should point to the data in that row (e.g. the recorded values for Tinus' weight). When you select rows of data, select all the way out to some distant column (e.g. column BZ) so that as you enter more data, the chart knows to expand.

  • Click Finish. You should now have a graph with all of the data in it, but with some visual flaws.

• The graph should have the weight lines looking nice, but all of the height and head data hugging the bottom because the cm numbers are so much lower than the gram numbers. To fix this, right-click on each of the lower data lines in turn, select Format Data Series, select the Axis tab, and select Secondary Axis. Now, you'll have an extra Y-axis on the right for cm, and the cm lines will scale to it.

• Right-click each of the six data lines, in turn, select Format Data Series, select the Patterns tab, and play with the color, style, and weight of the lines and points until you get them all looking nice. For example, you might want to have Tinus use dashed lines and squares for all of his lines, have Adrian use dotted lines and circles for all of his lines, and have the weight, height, and head lines each have their own color.

• Right-click somewhere in the whitespace around the chart, select Chart Options, and select the Titles tab. For Value (Y) axis, put "Weight (g)". For Second Value (Y) axis, put "Height and Head Circumference (cm)".

• You should now have a cool, readable chart.