Date/Time scales on graphs 1

[stduc]

I am trying to graph some data where the X axis is Date & Time. I want the X axis to include dates where there is no data and I want the time included. Is this possible?

If I select Time Scale I get a linear axis - but times are dropped. If I pick Time Scale I get the times, but a non-linear axis. There seems to be no way of selecting hours as the scale factor.

Is there a solution? Or am I limited to these choices?

--------------------------------------------------------------------------------
When I was single I looked for miss right. When I married her I discovered her first name was always.

 

[SkipVought]

Hi,

What application?

Skip,

_[red][/red]

 

[stduc]

Oops - sorry - Excel

--------------------------------------------------------------------------------
When I was single I looked for miss right. When I married her I discovered her first name was always.

 

[SkipVought]

Choose x-y scatter as the chart type.

on the x-axis scale, make the major unit eqaual to 1/24th of a day (one hours) .04167

Format the number on that scale as you wish to see displayed.

Skip,

_[red][/red]

 

[stduc]

Thanks for that - using a scatter would never have crossed my mind. Unfortunately using 1/24 of a day simply gives me too many tick marks to display. By the time I coarsen the scale to make it useful the times are meaningless again.

So I guess what I really want is tick marks only where there is a value. Is that possible?

--------------------------------------------------------------------------------
When I was single I looked for miss right. When I married her I discovered her first name was always.

 

[SkipVought]

There is no tic object in the object model. You could display the x-value LABEL for each point.

Skip,

_[red][/red]

 

[stduc]

There is no tic object in the object model.

As I feared - thanks for confirming that though.

You could display the x-value LABEL for each point.

I know - but that looks messy. As a compromise I've settled for drop lines. - Looks a bit 'nasty' though.

--------------------------------------------------------------------------------
When I was single I looked for miss right. When I married her I discovered her first name was always.

 

[SkipVought]

Here's another option.

Sub GridLinesForTimes()
    Dim r As Range, a(), i As Integer, j As Integer, k As Integer
    ReDim a(1, wsChartData.[DT].Count * 3 - 1)
    i = 0
    For Each r In wsChartData.[DT]
        For k = 0 To 2
            For j = 0 To 1
                Select Case j
                    Case 0
                        a(j, i) = r.Value
                    Case Else
                    Select Case k Mod 3
                        Case 1
                            a(j, i) = 1
                        Case Else
                            a(j, i) = 0
                    End Select
                End Select
            Next
            wsChartData.Cells(i + 2, "D").Value = a(0, i)
            wsChartData.Cells(i + 2, "E").Value = a(1, i)
            i = i + 1
        Next
    Next
End Sub

this generates data for another series that triplicates the date/time value (in my code its range wsChartData.[DT]) with values 0,1,0 - which created line spikes at each point. copy that data - select the chart and Edit/Paste Special - Select the New Series and assign to the SECONDARY axis with MAXSCALE = 1.

Don't think that this will LOOK so crappy as drop lines.

Skip,

_[red][/red]

 

[stduc]

OK - I sort of follow you - but would you be good enough to add a bit more detail? What did you name wsChartData.[DT]? Sorry but I'm a bit lost.

--------------------------------------------------------------------------------
When I was single I looked for miss right. When I married her I discovered her first name was always.

 

[SkipVought]

In the VB Editor, the Sheet Codename is wsChartData. If the sheet tab were named ChartData, then the code could also be Sheets("ChartData").

Then I used a header value of DT in A1 and then the date/Time values in A2 down. I use the Headings in row 1 as range names, by selecting the entire table, Insert/Name/Create - Create names in TOP row.

If you do that, then notice in the Name box to the left of the formula bar, that you have NAMES for each column in your table - select one and see what happens -- the NAME defines the range of the data.

So wsChartData.[DT] defines the range of date/time data in my sheet.

Skip,

_[red][/red]

 

[SkipVought]

This adds the new series automatically. BTW, it assumes that columns D & E are available in your sheet.

Sub GridLinesForTimes()
    Dim r As Range, a(), i As Integer, j As Integer, k As Integer
    ReDim a(1, wsChartData.[DT].Count * 3 - 1)
    i = 0
    For Each r In wsChartData.[DT]
        For k = 0 To 2
            For j = 0 To 1
                Select Case j
                    Case 0
                        a(j, i) = r.Value
                    Case Else
                    Select Case k Mod 3
                        Case 1
                            a(j, i) = 1
                        Case Else
                            a(j, i) = 0
                    End Select
                End Select
            Next
            wsChartData.Cells(i + 2, "D").Value = a(0, i)
            wsChartData.Cells(i + 2, "E").Value = a(1, i)
            i = i + 1
        Next
    Next
    With ActiveChart
        .SeriesCollection.Add wsChartData.[D1].CurrentRegion
        .SeriesCollection(2).AxisGroup = 2
        With .Axes(xlValue, xlSecondary)
            .MinimumScaleIsAuto = True
            .MaximumScale = 1
            .MinorUnitIsAuto = True
            .MajorUnitIsAuto = True
            .Crosses = xlAutomatic
            .ReversePlotOrder = False
            .ScaleType = xlLinear
            .DisplayUnit = xlNone
        End With
    End With
End Sub

Skip,

_[red][/red]

 

[stduc]

I am obviously missing the point somewhere. I can't get it to work. Can you post your workbook somewhere so I can take a look?

sendspace is as good as any - no need to register - just upload and post the link back here.

http://www.sendspace.com/

Thanks.

------------------------------------------------------------------------------------------------------------------------
When I was single I looked for miss right. It wasn't until after I married her I discovered her first name was always.

 

[SkipVought]

http://www.sendspace.com/file/xdcf03

Skip,

_[red][/red]

 

[stduc]

Many thanks for that - I can see how it works & where your coming from now. Just one last question I hope.

How did you name the Sheet Codename as wsChartData?

------------------------------------------------------------------------------------------------------------------------
When I was single I looked for miss right. It wasn't until after I married her I discovered her first name was always.

 

[SkipVought]

There are two ways that I know of.

1. Via code

Sheets("Chart Data").CodeName = "wsChartData"

2. Edit the Sheet Properties in the VB Editor -- there is the Name property and the (Name) property. The Name property is the tab name. The (Name) property is the CodeName.

I routinely edit the CodeName of most of my sheets, using a ws prefix as a naming comvention, and use that name in my code to reference the sheet object.

The benefit of that is twofold: it makes the code cleaner and it prevents errors from occuring in distributed code if users change the sheet tab name or tab position (some coders might also reference a sheet using the sheet index, Sheets(1) which can be screwed up if the sheet is moved)


Skip,

_[red][/red]

 

[stduc]

Duh - I knew that - I think I'm losing the plot.

Many thanks again for all your help.

------------------------------------------------------------------------------------------------------------------------
When I was single I looked for miss right. It wasn't until after I married her I discovered her first name was always.