How to set x-axis major grid intervals when scale is text

[petertn6]

I have a line chart where the x-axis is taken from a column containing the name of each row of accounting-month data (ie. not strict calendar months). I want a major grid line every twelve accounting months, but because the scale format is “text” this does not seem to be an option. I have tried re-defining the scale as “date” but this only offers grid intervals based on a number of days, not periods.

My requirement must be very common and it isn’t an issue in Excel, so I think I must be missing an obvious solution here.

In response to LeroyG, I am using version 6.4.4.2 and I am attaching a sample file (assuming my first attempt at this works)

New information: I have just discovered that the problem I am having disappears when I delete the final row of my spreadsheet (row 89 in the sample file). When I do this after following your instructions I can see monthly minor gridlines and yearly major gridlines exactly as I want them.

If it is any sort of clue as to what is going on, copy row 89 into a new row 90, and then delete 89 and this makes the chart work properly too, even though the data should be exactly the same. It is unfortunate that I tried to modify my chart immediately after both updating LibreOffice and also creating the new row in my spreadsheet, as I now have no idea which change gave rise to the issue.

Sample chart for AskLibreOffice.ods

[anon73440385]

What’s wrong with

• Select the x-axis
• Right click -> Insert Major Grid (this adds a grid line on each month)
• Right click -> Format Major Grid

?

[LeroyG]

petertn6,

Right-click on X-axis, select Insert Major Grid and Insert Minor Grid.

Right-click on X-axis, select Format Axis…; in Scale tab, select Type as Date, Major interval as 12 Months.

If the answer solved your question, mark the circle to the left. Do not use Answer to comment. You could edit your question, or comment an answer.

EDIT:
Content of cell A89 is in Text format, change to Date format, and retype the date.

[petertn6]

I did what you suggested, using an existing chart and after first deleting the major & minor grids that were already there. I also changed the Resolution to Months and formatted the Major Gridlines to be thicker than the Minor ones. However the result I get is still a grid line on every month, not one every twelve, even after deleting the minor grid again.

When I go back to the Format X-axis Scale tab I can see why nothing has changed - the Major Interval and Minor Interval Automatic check boxes have both reverted to having check marks in them, the Major Interval is 1 month and the Minor Interval is 2 months (sic). I can repeat your instructions any number of times and the interval settings always reset themselves to these values. And before anyone asks, yes, I am clicking OK and not Cancel!

[LeroyG]

@petertn6,

Can you share the chart? Delete all sensible data, replace with neutral text. Edit your question and use the paperclip.

Wich version of LibreOffice are you using (menu Help - About LibreOffice).

[petertn6]

I have edited my original question and I think I have successfully uploaded a sample file. I can’t see the file myself but I am not sure if I am supposed to. Please let me know if you can see it.

[anon73440385]

There is no attachment to your question - may be lack of karma, which I’ve provided now.

[petertn6]

I’ve just tried to upload again. I can browse to the file and select it, see an upload name in the box but there is no evidence that anything is happening when I clcik OK. In case you hadn’t guessed, thiis is the first time I’ve posted a question and I don’t know what a question with an attahment would look like. I also didn’t knw what a Karma was but I can see now that I’ve got one and it lets me do uploads.

[LeroyG]

@petertn6,

You will see an attachment some like C:\fakepath[a bunch of numbers].ods in blue.

[petertn6]

Looks like my upload has worked now.

[anon73440385]

@petertn6 - as @LeroyG stated your “date” in A89 is not a real calc date but text. Use View -> Value [x] Highlighting to find these kind of problems (text is black, numbers are blue and formulas are green). Dates in calc are integers counting the number of days since 1899-12-30.

[petertn6]

Opaque,
Thanks for that tip. I’ve only recently migrated this spreadsheet from Excel and the embedded Excel charts didn’t move across very well at all. I deleted them and created them from scratch in Calc and they were OK apart from this one issue with gridlines. I also continued to create new spreadsheet rows by copying and pasting without checking that Calc handled this operation in the same way as Excel. Turns out that sometimes it doesn’t, so I’ve learned something new and can finally think about not hanging on to Excel “just in case”. Thanks again to both of you.