I was suffering the same problem as the OP for many days after converting a “Columns + Line” chart to a “Stacked Columns + Line” chart when, Hey Presto!, all the formatted dates disappeared from the x-axis of the chart immediately after conversion, being replaced by numbers - the exact problem as the OP (see his CA.ods). Nothing I could do seemed to bring it back until I came across @Lupp’s answer on this page. However, I still had to spend a couple of hours trying to decrypt his answer, since I could not make any sense of “It’s getting what it is supposed to show from a range tiitled ‘Lables’ in th rtespective dialog”, so here is an expanded explanation for others as thick as me.
My spreadsheet is set up with:
- First row contains Headings for the data columns that are below them
- First column is headed ‘Date’ and contains formatted dates for each row of data to it’s right
Thus, in extreme simplified form, the SS looks like this:
Date Data1 Data2
Jan 1 12345 abcde
Jan 2 67890 wxxyz
In the middle of using the Chart Wizard we get to use the Data Ranges dialog. Another way to get there is to:
- Right-click on an existing Chart and choose ‘Edit’
- Right-click on the now-selected Chart & choose “Data Ranges”…
- On the first tab of the Data Ranges dialog (Data Range) put a tick✔ within both “First row as label” & “First column as label”
- On the second tab of the Data Ranges dialog (Data Series) place your cursor within the “Categories” section
- Enter the reference for the entire column of dates, NOT including the Header (eg for myself that is currently “$‘2020 Stats’.$A$2:$A$117”, or see Lupp’s chart within the SS in his answer)
- Repeat for each Data Series
- Press OK
The final item is:
- Right-click on an x-axis label & choose “Format Axis…”
(if the x-axis labels do not show, then right-click on the Chart, then select “X-Axis” in the top drop-down, then click on the “Format Selection” icon next to it (which gets the same dialog as previous), select the “Label” tab and put a tick in “
Show Labels” & press OK)
2. On the first tab (“Scale”) you can now choose either “Automatic”, “Date” or “Text”.
The difference is that “Date” will show an empty column for any missing dates, whereas “Text” will not.
A final shout-out & thank you to @Lupp for showing me how to fix a knotty problem in a simple fashion.
(tested using LO Version: 5.2.7.2 under Devuan Linux 2.1 (ascii))
If this helps then please tick the answer (
)
…and/or show you like it with an uptick (∧)
