Format correctly the dates in chart

[jcarlosd]

I am using Excel 2016 in Spanish.

I've created with VBA a simple xlLine Chart with dates in X axis and decimal values in Y-axis. The format of the SourceData range is set to dd-mm-yy by VBA code (myRange.NumberFormat = "dd-mm-yy").

When I move my mouse over a single point in the chart, a popup window appears with this content:
Serie "serie1" Punto "30-11-yy". Valor: 245

As you can see it is not showing correctly the year. Instead it shows a "yy". Perhaps it is because in Spanish the year format is "aa". But my source data was correctly formatted with VBA as dd-mm-yy.

Anybody can figure out how can I correct this inconsistency? I was trying also different setups with language preferences, but the result did not change, because the year always appear as "yy".

 

[Worf]

This works for me:

' Portuguese Excel 2013 version


Sub LineChart()
Dim shp As Shape, rng As Range
Set rng = Range("Data!$F$26:$G$31")
rng.Columns(1).NumberFormatLocal = "dd-mm-aa"
Set shp = ActiveSheet.Shapes.AddChart2(227, xlLineMarkers)
shp.Chart.SetSourceData Source:=rng
shp.Chart.HasTitle = 0
End Sub

 

[jcarlosd]

Finally I was able to "solve" this issue, that I am convinced that it is a bad behavior in Excel: for some reason that I don't know, if I try to set the NumberFormat to "dd-mm-yy" or the NumberFormatLocal to "dd-mm-aa" it did not work inside my macro, because it shows an incorrect format in the popup over the value.

I fixed it running another macro after running my macro to setup again the correct format. For me it is kind of strange, but at least it works.