Yes, there is a way to overcome this issue. You can use a combination of custom number formatting and a helper column to create monthly separation tick marks on the x-axis.
Here are the steps:
- Create a helper column next to your date column. In the first cell of the helper column, enter the formula "=TEXT(A2,"mmm yy")" (assuming your date column starts in cell A2). This will extract the month and year from the date and format it as "mmm yy" (e.g. "Aug 05").
- Copy the formula down the helper column for all the dates in your data series.
- Select your chart and go to the "Format Axis" pane.
- Under "Axis Options", set the "Minimum" to the first day of the first month in your data series (e.g. "01 Aug 05").
- Set the "Maximum" to the last day of the last month in your data series (e.g. "31 Jan 07").
- Set the "Major unit" to "1". This will ensure that there is a tick mark for every day on the x-axis.
- Under "Number", select "Custom" and enter the format code "mmm yy" (without the quotes). This will format the dates on the x-axis as "mmm yy".
- Under "Axis Options", set the "Tick mark type" to "Outside". This will ensure that the tick marks are visible outside the chart area.
- Under "Axis Options", set the "Tick mark spacing" to "1". This will ensure that there is a tick mark for every day on the x-axis.
- Under "Axis Options", set the "Tick mark labels" to "Low". This will ensure that the tick mark labels are aligned with the axis.
- Under "Axis Options", set the "Tick mark label position" to "Next to axis". This will ensure that the tick mark labels are visible next to the axis.
- Finally, hide the helper column by selecting it and setting the font color to white.
Now your chart should have monthly separation tick marks on the x-axis that are aligned with the first day of each month.