2

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.

1

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.

  • 1
    Thanks, these are good ideas and likely to help other users in this situation. After reviewing the options, I went in another direction - using daily averages for the chart, produced by a query command, since the amount of gathered data has increased a lot since this question was posted. – user79865 Feb 6 '16 at 20:51
0

Pretending that the data with the date/time is in column A - you can do an if statement to reformat the text for those like this:

=if(text(A1,"HH:MM:SS")="00:00:00",text(A1,"MM/DD/YYYY"),A1)

then use the new column to supply your data

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy