Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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
    ' Adjust constants as needed
    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. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Creating date/time graph (2000)

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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Creating date/time graph (2000)

    How about this?
    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. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Creating date/time graph (2000)

    Try an XY scatter chart.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #9
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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
  •