Linear date axis in pivot tables

Question

How do I format a PivotChart so that the horizontal axis is displayed in linear time?

See the PivotTable below as well as the source data (both excerpts).

If it is in fact not possible to create a linear-time PivotChart, what is the best way to convert my source data in order to achieve that?

Solution

The answer is: not possible to get a linear-time representation in pivot charts (if your source data steps are not linear)

As a workaround, you can create a standard XY/Scatter Chart from your Pivot Table data-either directly (reference the actual cells of the Pivot) or indirectly (use helper cells to transfer the values to your chart).

(from @dav's comments)

Comments

[dav]

Your question is a bit unclear, can you please elaborate-the time is sorted in the same manner as your pivot table (that's built into pivot charts). And it appears that it is chronological (which I assume you also want). However, pivot charts use a line chart type, and that assigns the x-axis values as categorical data, which means the values are handled basically as labels. If you want an appropriate gap between date values (e.g. 3 spaces for 3 days), you'll need to use an XY/Scatter chart, which cannot be done as a Pivot Chart.

[Riley]

That answers my main question, thank you! So it is simply not possible to get a linear-time representation in pivot charts unless your source data has entries for each increment.

[dav]

Unfortunately, that's correct. However, unless you specifically want a Pivot Chart (and I personally can't think of a good reason to), you can create a standard XY/Scatter Chart from your Pivot Table data-either directly (reference the actual cells of the Pivot) or indirectly (use helper cells to transfer the values to your chart). If you want help with either of those, reword your question to fit, and I'll be happy to help.