Ask Your Question

How to create a chart with date-time values.

asked 2019-07-03 13:57:26 +0100

TimLee gravatar image

updated 2020-07-21 13:32:57 +0100

Alex Kemp gravatar image

I'm trying to create a chart where the x-axis is date time values, but the chart always seems to line up all y values for each date with the date only, no separation by the fraction for time. How can I make it use the full value?

edit retag flag offensive close merge delete


This question lacks plenty of information. You did not specify

  • Your OS version
  • Your LibreOffice version
  • The type of chart you are using [XY (Scatter)?]
  • The exact format of your date time values (as per format specification)
  • The chart you get and how you want to have it look like (at least for me)

In general: LibreOffice Calc uses the format of the data as specified in cells by default, when creating a chart using the chart wizard. But you can change the format by unticking Source Format in the Format Axis dialog of the X-Axis (double click on X-axis will open the dialog) and defining your own format for the X-axis (Tab: Numbers)

Opaque gravatar imageOpaque ( 2019-07-03 14:37:17 +0100 )edit

ok - OS Windows 10 & Libre Office Version: 6.2.4 The type of chart - preferably line The format I did say were datetime values - numeric values such as 43643.265150463 which with a format mask of time: dd/mm/yyyy hh:mm shows up as "27/06/2019 06:21" I have a few data points every hour for a week. Ideally I'd like the x axis to show the dates & have the points on the lines spread across the space for the day. Instead I'm getting it truncating the values to the integer date. All Y points align to the midnight time. Instead of a roughly smooth line I have a staircase. It's not that the format doesn't show, it's that the chart has truncated the date+time values to dates with no time.

TimLee gravatar imageTimLee ( 2019-07-03 15:20:04 +0100 )edit

I'd use "XY (Scatter)" which allows you to define the format as per my comment.

Opaque gravatar imageOpaque ( 2019-07-03 15:25:46 +0100 )edit

5 Answers

Sort by » oldest newest most voted

answered 2019-07-03 15:43:45 +0100

Opaque gravatar image

updated 2019-07-03 16:54:38 +0100


for illustration purposes an XY (Scatter) graph for one week / each hour a random number between 0 and 10 / resolution 02:00 hours

image description

Update Testing OPs data:

image description

I cannot confirm any change in values.

edit flag offensive delete link more

answered 2019-07-03 14:43:19 +0100

pierre-yves samyn gravatar image


Unfortunately, if I understand the question correctly, the smallest resolution is the day:

image description


edit flag offensive delete link more


These are the options for Chart Type "Line", but check also "XY (scatter)"

Opaque gravatar imageOpaque ( 2019-07-03 14:56:35 +0100 )edit

@Opaque - yes my answer assumed OP wanted Line type

pierre-yves samyn gravatar imagepierre-yves samyn ( 2019-07-03 15:32:29 +0100 )edit

It's not the formatting of the axis, but the error introduced in the values that's the issue.

TimLee gravatar imageTimLee ( 2019-07-03 16:33:03 +0100 )edit

answered 2019-07-03 20:03:55 +0100

Ratslinger gravatar image


There is a bug using Line chart and Date/time labels. Can be fixed. Don't know if bug was ever reported. See my answer here -> Why won't my Calc barchart show proper time with date?.

With seting the first Timestamp field as a text field (kludge) all seems to work:

image description

edit flag offensive delete link more


Hmm ... please help me understanding the use of dates in equidistant date ticks in a line chart. This suppresses any visual information about the time distance (difference) of subsequent data points. For sure this isn't a matter, if times are equidistant from each other, but what, if not. This may cause a wrong impression

Opaque gravatar imageOpaque ( 2019-07-04 09:57:43 +0100 )edit

answered 2019-07-03 19:29:34 +0100

ve3oat gravatar image

Each spreadsheet program seems to use different methods for handling dates and times and their various formats. To avoid having to learn a different method for each program, you could calculate your own fractional Day of Month (DoM) value for your observations, and plot your y-values against the corresponding calculated DoM value.

In LibreOffice, if the date is in column A and the time is in column B, the DoM for row 9 can be calculated from =DAY(A9)+HOUR(B9)/24+MINUTE(B9)/1440

Be sure to use the x-y (scatter) diagram and plot your y-values against the calculated DoM as x.

You can use as many or as few significant digits as necessary for accuracy and can add a term for SECONDs if required by your data (+SECOND(B9)/18600).

edit flag offensive delete link more

answered 2019-07-03 16:28:45 +0100

TimLee gravatar image

updated 2019-07-03 16:36:40 +0100

OK - i have a solution and a problem. The solution is to use XY(Scatter) which is not initially clear because I don't want a scatter plot, I want a line, it is a continuous function, but the interpretation of 'line' category is assuming the X axis is merely labels, not values. Ok.

But there's some odd behaviour evident in the line option. I made a tiny dummy spreadsheet

time            value
03/07/19 06:00  2
03/07/19 07:03  3
03/07/19 12:30  4
03/07/19 22:33  5
04/07/19 01:27  6
04/07/19 15:33  5
05/07/19 15:23  4
06/07/19 08:09  5
06/07/19 13:01  6

insert- chart, select line type, it shows the gathered values. The first 4 y points all on 03/07 are lumped on 03/07 00:00 But if I select next & on the Data range page uncheck First Row as Label, it then assigns each point a distinct x axis position. They are not unevenly space like the XY plot, but at least they are unique. Values shouldn't be changed by the assignment of a label, this would seem to be a bug. image description

edit flag offensive delete link more


Please do not post "Answers" if you actually don't answer a question. And if you feel something is a bug please file here. Now it starts to be a problem which cannot be analyzed without havin an example of your data. Please consider to upload an anonymized version.

Opaque gravatar imageOpaque ( 2019-07-03 16:35:15 +0100 )edit

My "answer" is a better understanding of how Libre Calc describes the chart types. I'd more or less ignored the type labelled "XY (Scatter) because I didn't want a scatter chart, I wanted a line of a continuous function. But "Line" type isn't quite that. Your suggestion to use "XY (Scatter)" ultimately resolves my initial issue. Thanks for the help. I had become distracted by the bizarre behaviour within the line chart type.

TimLee gravatar imageTimLee ( 2019-07-03 16:50:00 +0100 )edit

Please check the update in my answer. I have added the XY chart for your test data and I cannot confirn what you called are lumped on 03/07 00:00.

Opaque gravatar imageOpaque ( 2019-07-03 16:57:01 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-07-03 13:57:26 +0100

Seen: 4,553 times

Last updated: Jul 03 '19