Date units not displaying correctly on chart axis

I created a pivot table with dates in the first column (starting with 01/06/21), and four columns of data by type. I then created an XY Scatter chart (graph) with lines from this table, which plotted up nicely. However, the x-axis (dates) displayed as integer units starting at 0… so I’m guessing it’s just counting row numbers.

The “Numbers” tab under “Format Axis” had option “Source Format” checked by default, and I confirmed that the dates in the pivot table were indeed formatted as dates (MM/DD/YY). When I un-checked “Source Format” and chose “Date” as the Category in that dialog window, I got dates on the x-axis but they started at 12/30/99!

What I am doing wrong here?

1 Like

All in all I assume your original data do not contain real calc dates but text looking like dates. Check with View -> Value Highlighting whether your “dates” show in blue color (=real calc dates) or in black color (=text). More could be analyzed only, if you upload and anonymized sample file. Edit your question and user the paperclip symbol to do so.

Please do **not** use *Add Answer*, since answers are reserved for solutions to a problem on this Q&A site. Thanks in advance …

Thank you for your rapid response and the nice suggestion! I confirmed using your method that all date values in the source table as well as the pivot table are indeed formatted as dates (blue).

Ok - then the correct type of chart definitely is XY (Scatter), since you need to have numerical data (dates are integer values) on the x-axis, while all other chart types use categories on the x-axis. If your chart starts at 0 (which would be 1899-12-30) then check the data range you defined and whether it contains a 0 somewhere. You may also want to adjust Scale (Format -> Axis.. -> Tab: Scale) by unchecking [x] Automatic for Minimum.

The chart x-axis auto-sets 0 as the minimum for the integer scale, but the first data points (for 01/06/21) are plotted at 1. De-selecting “Source Format” makes the first data points line up with 12/31/99 somehow. Strange. No zeroes in the source table or pivot table, either…

Without a sample sheet showing the problem, I can’t help any further. Everything keeps to be guesswork …

Hi PTF, I have the exact same issue now and I think I found the solution. I am not sure if you are still using LibreOffice but hopefully, this solution help other people who have the same issue.

So at the beginning, the column plot, and the XY Scatter plot both displayed my X-axis as integer units starting with 0. Same as your case. Then I double-checked my date column format and it is indeed date (yyyy-mm-dd). I even tried to convert the date format to (mm/dd/yyyy) but no help. I also tried copying the columns to a different sheet (but still under the same ODS file) however, the plot remained the same.

Next, I copied the columns to a separate ODS file and pasted only values. The plot works this time! The Calc recognized the format and displayed it in the right way with its default parameters. I started thinking about how can I do the same thing in my original ODS file.

Here is the actual solution:
Select the date cells (for me, A2:A31), copy
Right click on an empty cell, then paste special → Unformatted Text
In the opened Text Import window, locate the Fields section at the bottom of the window. Click on the date column and the Column type drop-down menu will be active. Select the corresponding format for your case (mine is Date(YMD)) from the menu. Then click OK.
Now you can use this newly pasted column as your date column and the chart should work for you now. You can also cut and paste this newly generated column back to the original date column (overwrite it) so you still have your sheet well-organized.

By playing with the X-axis of the plot, you can even turn it into a better view with a major grid that divides the plot based on months. Like the plot on the bottom.

I guess the reason is that the right-click menu → Format Cells does not correctly convert the date. Although when you check the converted cells it says Date as its format, the cells can not recognized by the chart. Copying and Pasting unformatted text guarantees the format is indeed Date.