Excel – Horizontal date axis incorrect on Excel line chart with secondary axis

I think I have a solution for you: Add a secondary horizontal axis.

The axis setting "Horizontal axis crosses" for the primary vertical axis only affects the primary horizontal axis. The setting for the secondary vertical axis only affects the secondary horizontal axis. It doesn't look like you need a secondary horizontal axis in this case so you can turn it on, set its crossing point to 0, and then hide the labels and tick marks.

Example in Original Condition:

(Chart Tools) Design > Add Chart Element > Axes > Secondary Horizontal

Format Secondary Vertical Axis > Axis Options > Horizontal Axis Crosses > Axis Value = 0

Format Secondary Horizontal Axis > Labels = None, Tick Marks = None, Lines = Black Solid 3.0pt Dashed

Excel (thru 2010, not sure about 2013) won't do this natively. However, there are two one way to get the same visual effect (actually my second idea wouldn't meet your goals, so please disregard).

Use an XY Scatter chart instead of a line chart for your Claim data. This way you can explicitly define the location of each point along the horizontal axis. Here's how it will look:

Here's the general steps:

1. Create a chart with your 6 data series.
2. Assign the Claims series to a secondary axis.
3. Add your horizontal and vertical secondary axis to the chart (not necessary, but visually it helps align everything).
4. Change the chart type to XY/Scatter for your Claims series. This will change their current values to their new vertical values-their horizontal values will be blank, which defaults to the equivalent of {1,2,3,4,5}.
5. Add a column for the horizontal values for each series. In your case you'll have 3 columns of 5 values (matching the set of vertical values you have).

Now the tricky formatting part:

6. To simplify our math, in your column chart formatting, change the Gap Width to 100%.
7. Now, Chart Math. If you look at the image, you'll notice that Excel puts 3 columns in each category. It also puts a gap between the series groups. It splits the Gap Width (100% of one column, unless you skipped step #6 in which case your math is harder!) evenly between the first and last column of the group. So, the center of the first column is at 0.25 of width of the first category group, and 0.50 for the second column, 0.75 for the third column. The horizontal value of 1.0 falls exactly halfway between the first and second category group. So, the horizontal values for your first series are {0.25,1.25,2.25,3.25,4.25}. Your second series will increment up from 0.50 and the third from 0.75.
8. Now just edit your Claims series with your horiztonal values (either in reference to cells, or in a static set (not recommended)).
9. Format to taste.

Here's the data included with the chart:

FWIW, this is an extremely powerful technique that will allow you to create almost any custom chart you'll want or need (with Excel!). It's worth some experirmentation to see how you can configure different parts.