Changing x axis type to "date" when it contains data from 2 columns

Hi,

I have a line/bar graph with an X-axis made up of 2 different columns - a "date" and a "type". This makes it easy for me to tell what type the bars and lines correspond to, which is great. However, I wish to format the axis as a date so that the data points are proportional to the date on the axis. I'm unable to do so as this option is greyed out.

If I remove the "type" column from the chart data area, then it automatically recognizes the dates and plots the data points proportionally on the axis. However, it also includes fills in the axis with dates that are not in my data. My x-axis consists of discrete events that I wish to plot rather than a continuous one.

How can I do this?

I would suggest that you provide a better example (an Excel file uploaded to the forum would be ideal) of what you have tried along with your explanation of what you want. I am having difficulty visualizing what you want.

Your observation that date and multi-level category axes do not work together is correct. If I understand what you are trying to do here, one would usually "hide" the real axis (format as no line, no marker, no label), then add a "dummy" series containing the desired points, and format that second series so it looks like the axis. The basic technique is illustrated here http://peltiertech.com/reciprocal-chart-axis-scale/ for a "reciprocal" axis on a scatter chart, but the technique should readily adapt to a line chart with a date axis.

It is clearer, though we cannot do much with a screenshot other than look at it.

As I suggested, and as you have already seen, in order to get a date axis, you need to get rid of the "multi-level" horizontal axis -- which means that your horizontal axis data can only be based on column A. You will need to figure out a different strategy for identifying the Type 1 and Type 2.

One strategy might be to use column A for the horizontal axis data, columns D, F, H (G must be hidden) for the column chart data, then use data labels to identify the Type 1 and 2 (https://support.office.com/en-us/art...2-f467c9f4eb2d ).

Another strategy might be to change the format for the type 1 and/or type 2 columns so that the color/fill pattern/?? will be used to identify type 1 and type 2. This is probably most easily accomplished using "conditional formatting" of charts (which is mostly done in the spreadsheet, not the chart): http://peltiertech.com/conditional-f...-excel-charts/

I would probably pick one of those two strategies and work with that.

You can add 2 dummy series and use the data label to provide the type.

Although the date axis provides the proportion spacing it means it get very condensed around actual data points.