Date units not displaying correctly on chart axis

asked 2021-01-17 18:21:56 +0200

PTF gravatar image

updated 2021-01-17 18:43:22 +0200

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? Thanks!

edit retag flag offensive close merge delete


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 …

Opaque gravatar imageOpaque ( 2021-01-17 18:33:09 +0200 )edit

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).

PTF gravatar imagePTF ( 2021-01-17 18:42:17 +0200 )edit

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.

Opaque gravatar imageOpaque ( 2021-01-17 18:59:04 +0200 )edit

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...

PTF gravatar imagePTF ( 2021-01-17 21:00:46 +0200 )edit

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

Opaque gravatar imageOpaque ( 2021-01-17 23:01:38 +0200 )edit