[SOLVED] Timescale chart interpreting dates wrong

[Jape]

I have a chart wich i want to display the complete current year. I want to populate this with dates with varying intervals (between, say 7 - 40 days).
When i fill the graph with 7 dates in Jan, Feb, and March, and change to timescale the dates seem to be plotted as months. Ive checked the number format. Whenever i switchh back to category it shows my dates correctly. How do i set it up so i can see the total year with the dates interval plotted on the correct dates ??

Jape,

 

[SydneyGeek]

If you want to scale dates correctly on the X axis, use XY Scatter as the chart type.

Any other chart type will treat dates or numbers as categories, and space them evenly.

Denis

 

[Jape]

thanks ,I have tried but then the timescale option for the x axes values is grayed out/ can't be selected. Could the problem be im populating the seriescollection and xvalues seperatly trough vba instead of a nicely formatted range on a sheet as source ?

Jape,

 

[SydneyGeek]

Are you using an XYScatter plot, or just the default (which will likely be a column or line chart?)

Line and XYScatter are NOT the same

Denis

 

[Jape]

nope, im definatly using an xy scatter chart, subacategory -> data connected by lines though, but i assume thats not what you're referring to.

Jape,

 

[SydneyGeek]

Jape,

I'm having trouble visualising this. Could you post a chunk of your data?
Either use Colo's HTML Maker or Erik van Geit's Table-It
Denis

 

[Jape]

Ok ill show the process and problem im having. The following code is used to get all data in the chart.

'Area Chart FINANCES YEAR
If OP_FinancesYear.Value = True And LB_FinanceCats.ListIndex = 0 And TB_StackedFinances.Value = True Then


    Worksheets("TempG").ChartObjects("FinanceMonth1cat").Chart.SeriesCollection(1).Name = LB_FinanceCats.Column(0, LB_FinanceCats.ListIndex)

Worksheets("TempD").Activate
Range("A2").Activate
ReDim dt(1 To 1)

'===============================================
'============  Populates the Dynamic Range of Dates =========
'===============================================
Do Until ActiveCell.Value = ""
dt(UBound(dt)) = Day(ActiveCell.Value) & "-" & Month(ActiveCell.Value)
ActiveCell.Offset(1, 0).Activate
If Not ActiveCell.Offset(1, 0).Value = "" Then ReDim Preserve dt(1 To UBound(dt) + 1)
Loop


'===============================================
'============  Use that Range to Populate Data for Dates  ======
'===============================================
ReDim d(1 To 12, 1 To UBound(dt))
j = 0

For i = UBound(dt) To 1 Step -1
d(1, i) = Worksheets("TempD").Range("P65000").End(xlUp).Offset(j, 0).Value - Worksheets("TempD").Range("P65000").End(xlUp).Offset(j - 1, 0).Value
d(2, i) = Worksheets("TempD").Range("O65000").End(xlUp).Offset(j, 0).Value - Worksheets("TempD").Range("O65000").End(xlUp).Offset(j - 1, 0).Value
d(3, i) = Worksheets("TempD").Range("N65000").End(xlUp).Offset(j, 0).Value - Worksheets("TempD").Range("N65000").End(xlUp).Offset(j - 1, 0).Value
d(4, i) = Worksheets("TempD").Range("R65000").End(xlUp).Offset(j, 0).Value - Worksheets("TempD").Range("R65000").End(xlUp).Offset(j - 1, 0).Value
d(5, i) = Worksheets("TempD").Range("AF65000").End(xlUp).Offset(j, 0).Value - Worksheets("TempD").Range("AF65000").End(xlUp).Offset(j - 1, 0).Value
d(6, i) = Worksheets("TempD").Range("S65000").End(xlUp).Offset(j, 0).Value - Worksheets("TempD").Range("S65000").End(xlUp).Offset(j - 1, 0).Value
d(7, i) = Worksheets("TempD").Range("X65000").End(xlUp).Offset(j, 0).Value - Worksheets("TempD").Range("X65000").End(xlUp).Offset(j - 1, 0).Value
d(8, i) = Worksheets("TempD").Range("AE65000").End(xlUp).Offset(j, 0).Value - Worksheets("TempD").Range("AE65000").End(xlUp).Offset(j - 1, 0).Value
d(9, i) = Worksheets("TempD").Range("L65000").End(xlUp).Offset(j, 0).Value - Worksheets("TempD").Range("L65000").End(xlUp).Offset(j - 1, 0).Value
d(10, i) = Worksheets("TempD").Range("Q65000").End(xlUp).Offset(j, 0).Value - Worksheets("TempD").Range("Q65000").End(xlUp).Offset(j - 1, 0).Value
d(11, i) = Worksheets("TempD").Range("M65000").End(xlUp).Offset(j, 0).Value - Worksheets("TempD").Range("M65000").End(xlUp).Offset(j - 1, 0).Value
d(12, i) = Worksheets("TempD").Range("T65000").End(xlUp).Offset(j, 0).Value - Worksheets("TempD").Range("T65000").End(xlUp).Offset(j - 1, 0).Value
j = j - 1
Next


'===============================================
'============  Puse All Arrays Into Chart =================
'===============================================
ReDim dMIN(1 To UBound(d, 2))
Worksheets("TempG").ChartObjects("FinanceYear").Chart.SeriesCollection(1).XValues = Array(dt)


For i = 1 To 12
    For j = 1 To UBound(dMIN)
        dMIN(j) = d(i, j)
    Next
        Worksheets("TempG").ChartObjects("FinanceYear").Chart.SeriesCollection(i).Values = dMIN
Next


'============  Copy chart on Useform  ==================
'===============================================
    Worksheets("TempG").ChartObjects("FinanceYear").Chart.CopyPicture xlScreen, xlPicture
    Main.PB_CurrentFinance.Picture = PastePicture(xlPicture)

End If

Wich gives me the data in the chart,. ive taken screenshots of my setting for the chart and the result it produces.

hmm small correction btw, the Dates are passed from an Array of type Long(makes no sense i know ), but that means they get passed as an excel date serial, wich means the dates MUST be recognized, hmm im out of thoughts on this one...

EDIT:, nevermind, got confused, IS passed as an DATE array
Jape,

 

[SydneyGeek]

Hi Jape,

Try changing this line...

dt(UBound(dt)) = Day(ActiveCell.Value) & "-" & Month(ActiveCell.Value)

to this...

dt(UBound(dt)) = Activecell.Value

or...

dt(UBound(dt)) = CDbl(Activecell.Value)

I find that pulling non-standard date formats into Excel or Access is asking for trouble. Once the chart is created, you can format the X-axis as dates.

Denis

 

[Jape]

Hi,

Actually the following code was already my initial one, but forgot to change back when experimenting

dt(UBound(dt)) = Activecell.Value

But Cdbl ive tried too now, unfortunatly also no succes. However when i go back to my chart and look in the Source Data -> Category X label series, the data has been pushed in correctly, altough the problem could be that it treats/interprets the dates as strings when between notation marks( " " )

={"26-1-2006"\"2-2-2006"\"9-2-2006"\"16-2-2006"\"2-3-2006"}

Thanks, Jape,

 

[SydneyGeek]

Yes, it looks like VBA is possibly handing the array to the chart as text values.

I ran a test, pushing the values out to a worksheet, and the dates behaved as dates in that context and plotted as dates as well.

Maybe you need to write the arrays to a hidden sheet as a workaround, and use that sheet as the source for the chart.

This code comes after the code that builds the main array --

For i = 1 To 12
    For j = LBound(dt) To UBound(dt)
        Sheets("Test").Range("A1").Offset(i, j).Value = d(i, j)
    Next j
Next i
Sheets("Test").Range("B1").Resize(1, UBound(dt)) = dt

Denis