Setting a chart y-Axis on a Secondary Axis - breaks the x-Axis formatting

[morsagmon]

Hi.

I have a simple, two lines chart.
My X-Axis range has dates, linked to source.
As soon as I define one of the y-axis lines to be on a secondary axis - the dates formatting of the x-Axis are removed and all labels are shows as a sequence of numbers: 1 2 3 4 5 6...
Setting it back on the primary axis - shows the X-axis labels correctly.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]X-Axis [/TD]
[TD="align: center"]Y-Values1[/TD]
[TD="align: center"]Y-Values2[/TD]
[/TR]
[TR]
[TD="align: right"]Jan-17[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]4658[/TD]
[/TR]
[TR]
[TD="align: right"]Feb-17[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]6845[/TD]
[/TR]
[TR]
[TD="align: right"]Mar-17[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]7651[/TD]
[/TR]
[TR]
[TD="align: right"]Apr-17[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]3486[/TD]
[/TR]
</tbody>[/TABLE]

I tries forcing x-Axis Type to be Date Axis - no effect.
Any ideas on how to retain the X-axis formatting to be linked to source and show the dates, while two Y-axis in effect?

 

[thisoldman]

I think you are using a line chart, correct?

I believe your dates are text, not Excel serial number dates. Try this:

Change the first date to a true Excel date: 01/01/2017.
Right-click and drag from that cell downward. Choose "Fill Months" from the popup context menu.
Format the date cells using a custom number format, mmm-yy
Now create your line chart.

 

[morsagmon]

Thanks, thisoldman.
The cells are formatted as dates. Not only they are formatted as dates, I'm alternating their formatting between "[$-40D]mmm-yy;@" (if chart showing last 12 months) and "yyyy" (if chart showing last 5 years) - this is done in VBA and the cells comply beautifully. Also, note that when both lines are on the primary axis, the current date format of the cells is applied to the x-Axis chart just fine.

 

[thisoldman]

I did not have a problem when I manually create the chart. You probably want to post the VBA code so that someone can take a look. My own VBA skills are not that good.

 

[morsagmon]

OK,
I recreated the chart from scratch (one more time), at the same sheet where the staging data is, and it now works. I moved it to the dashboard sheet - still works!
Let's hope that glitch is gone forever...
Thank you!

 

[thisoldman]

Good to hear! Intermittent errors are the worst to troubleshoot—I'll keep some virtual fingers crossed.