# Thread: Creating date/time graph (2000)

1. ## Creating date/time graph (2000)

I have dates 25/1/05, 26/1/05 etc in column A and times 0:00, 0:30, 1:00 running across row one. I want to create a graph which plots the data as the y-axis and the time series as the x-axis.
In order to do this I'm going to re-create the full set of date/time values in a column, with the data values extracted in an adjacent column. What formula can I use to extract the data values from the table? That is, to convert a table of values to a single column? Thanks, Andy.

2. ## Re: Creating date/time graph (2000)

I would use a small macro to do the transformation. Assuming that the dates are in A2:Annn and the times in B1:X1:

Sub Transform()
Dim i As Long
Dim j As Long
Const NumDays = 37
Const NumTimes = 48
Const Correction = -3
For i = 2 To NumDays + 1
For j = 2 To NumTimes + 1
Cells(NumTimes * i + j + Correction, 1) = Cells(i, 1) + Cells(1, j)
Cells(NumTimes * i + j + Correction, 2) = Cells(i, j)
Next j
Next i
End Sub

3. ## Re: Creating date/time graph (2000)

I can't use a macro to achieve this, it needs to be done using a formula. Andy.

4. ## Re: Creating date/time graph (2000)

Why not? The macro need not be stored in the workbook itself.

5. ## Re: Creating date/time graph (2000)

I assume in Sheet1 that A2:A500 has dates (=499 dates)
Sheet1 B1:AW1 has 0 to 23:30 in 30 min increments (48 values)

In sheet2 A1 enter:
Date/time

In Sheet2 B1 enter:
Lookup Value

In Sheet2 A2 enter:
=INDEX(Sheet1!\$A\$1:\$A\$500,INT((ROW()-2)/48)+2)+INDEX(Sheet1!\$B\$1:\$AW\$1,MOD(ROW()-2,48)+1)

In Sheet2 B2 enter:
=INDEX(Sheet1!\$B\$2:\$AW\$500,INT((ROW()-2)/48)+1,MOD(ROW()-2,48)+1)

Copy A2:B2 to A3:B23953 [note 23953 = (500[=last data row]-1)*48+1

Change ranges as desired...

Steve

6. ## Re: Creating date/time graph (2000)

Brilliant thanks!!
I've got two columns now of dates and times and corresponding values that I can graph. However, creating a line graph with the time series along the x-axis, Excel isn't allowing me to scale it other than by Day(s), Month(s), or Year(s). Should I use a different graph type? Andy.

7. ## Re: Creating date/time graph (2000)

Try an XY scatter chart.

8. ## Re: Creating date/time graph (2000)

Use an X-Y scatter. You can format the "X-Axis" to whatever you want and display as appropriate. You can set the major/minor units as desired (even in fractions). The dates are in units of "days"

Steve

9. ## Re: Creating date/time graph (2000)

Almost there.. I've got an X-Y scatter graph. I've got the major units as 1 (representing 1 day) and the minor units as 0.020833 (representing 1/48th of a day - that is half-hourly). I've displayed the minor tick marks. My question is, how can I display the date/ time with the minor tick marks? It only displays the date/ time at the major tick marks? Thanks, Andy.

10. ## Re: Creating date/time graph (2000)

Wouldn't it get very crowded with all those labels? Apart from that, Excel only displays labels for the major tick marks. Here is the reply from Excel MVP Jon Peltier to a similar question in the Excel newsgroups:
<hr>Nope. Only major tickmarks get the labels. You could promote the minors to major status, which you probably don't want to do. Or you could put a dummy
XY Scatter series along the X axis with a point at each minor tick mark, add labels to these points, using the "Show labels" option. Then format the series patterns to hide the new series: no lines and no markers.<hr>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•