[SOLVED] Timescale chart interpreting dates wrong

Jape

New Member
Joined
Sep 30, 2004
Messages
47
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,
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

New Member
Joined
Sep 30, 2004
Messages
47
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

New Member
Joined
Sep 30, 2004
Messages
47

ADVERTISEMENT

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,
 

Jape

New Member
Joined
Sep 30, 2004
Messages
47

ADVERTISEMENT

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

Code:
'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.

chart.jpg


chart2.jpg


chart3.jpg



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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Jape,

Try changing this line...
Code:
dt(UBound(dt)) = Day(ActiveCell.Value) & "-" & Month(ActiveCell.Value)

to this...
Code:
dt(UBound(dt)) = Activecell.Value

or...
Code:
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

New Member
Joined
Sep 30, 2004
Messages
47
Hi,

Actually the following code was already my initial one, but forgot to change back when experimenting
Code:
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( " " )
Code:
={"26-1-2006"\"2-2-2006"\"9-2-2006"\"16-2-2006"\"2-3-2006"}

Thanks, Jape,
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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 --
Code:
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
 

Forum statistics

Threads
1,141,586
Messages
5,707,230
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top