Home » Questions » Computers [ Ask a new question ]

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

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

I recently became a father to twin boys. Naturally there is a lot of weighing and measuring, and we record these numbers in a spreadsheet. I use OpenOfficedotorg Calc (but I'm open to solutions for Microsoft Excel/GoogleDocs/AnyOtherSpreadsheet), and I want to create a visual interpretation of their growth.

Asked by: Guest | Views: 85
Total answers/comments: 2
Guest [Entry]

"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."
Guest [Entry]

"I suppose by now your boys are already in school. But if you are still logging their growth (or for other similar problems), I have an not bad solution. Here is an example chart that I made based on your description and sample data.

In this example chart, the two lines show the weights of the boys. And the columns show the heights and head length. You could also see the the proportion of the head to the body. The date on the x-axis is in correct scale even if you are not making the measure for regular intervals. If you hover your mouse on the chart you could also view the info of measurement points.

As you could see there is some code in the Excel, this is because I made this example chart with the help of the Funfun Excel add-in. This add-in allows you to use JavaScript directly in Excel so that you could make use of libraries like HighCharts.js or Chart.js to make this kind of chart easily.

As for your data, you could just store them as the way you have in spreadsheet. But you do need to change a little bit of the format of your date. It's better to make them as dd/mm/yyyy or dd-mm-yyyy, this is because I'm using the code below to parse the date in spreadsheet into datetime format of JavaScript.

for(var i=1;i<data[0].length;i++){
date.push(Date.UTC(parseInt(data[0][i].split('.')[2]),parseInt(data[0][i].split('.')[1])-1,parseInt(data[0][i].split('.')[0])))
}

The Funfun also has an online editor in which you could explore your JavaScript code and result. You could check the detail of how I made the example chart in the link below.

www.funfun.io/1/#/edit/5a4caabd06791937c4134885

Once you are satisfied with the result you achieved in the online editor, you could easily load the result into your Excel using the URL above. But of course, first, you need to add the Funfun add-in into your Excel by Insert - Office Add-ins. Here are some screenshots showing how you load the example into you Excel.

I hope this would still be useful for you :)

Disclosure: I'm a developer of Funfun"