Google Sheets – Suppress Time Part of Datetime Values on Chart Axis

google sheetsgoogle-sheets-charts

I have a line chart based on the following data, capturing the Close Queue size every four hours: 

+---+-------------------+-------------+
|   |         A         |      B      |
+---+-------------------+-------------+
| 1 | DateTime          | Close Queue |
| 2 | 9/28/2015 0:17:54 | 87          |
| 3 | 9/28/2015 4:17:53 | 80          |
| 4 | 9/28/2015 8:17:54 | 64          |
| 5 | ................. | ........... |
+---+-------------------+-------------+

I'm fine with the chart automatically placing the tickmarks and their labels on the time axis, but the presence of 0:00:00 on each label is annoying. It's just noise, not adding any information: 

10/6/2015 0:00:00     10/13/2015 0:00:00     10/13/2015 0:00:00

I tried formatting column A as date without time. Then the labels don't contain time, but all values from the same day get plotted on the same vertical line: i.e., time is completely ignored.

Is it possible to suppress time part on the axis, but still have it taken into account by the chart itself?

Manual edit of the chart seems out of question: as more data arrives, the values corresponding to axis tickmarks keep changing.

Best Answer

Short answer

  1. Set the format of timestamp values as Date only.
  2. Activate the "Treat labels as text" chart option.

Instructions

  1. On the chart drop-down menu, select Advanced Edit
  2. Click on the Customization tab
  3. At the left panel, scroll down until you see the section called "Axis 1. Horizontal"
  4. Check the "Treat labels as text" checkbox.
  5. Click the "Update" button.

Alternative

Instead of using a Line chart use the Time Line Chart, named as Annotated time line in Chart types - Google Docs editors Help. The look and feel of this chart was not updated yet, but it still works and do a great job handling date-time labels for the horizontal axis.

Related Topic