Plot with hours that have a sign on Libreoffice-Calc

On Libreoffice calc I have a spreadsheet that can compute certain quantities in time (hours). These quantities can be positive or negative.

For example, the values in the spreadsheet can be a daily commuting time to work minus an average/nominal value. Thus a negative value in a certain day would indicate that the trip on that day was faster than usual.

I have also created a plot to visualize this, but I cannot put the correct value on the y-axis. Somehow the values are set with a circular algebra, and if they are above 24 hours the labels on the y axis are resets to 0. Also, the negative values are shown as positive values, while I would like to show them with their sign.

Any clue on how to correctly choose the labels for the y-axis?

In the figures you see the plot, the y-axis options for that plot and part of the data used for the plot. As you can see, the values on the H column are measured in hours and minutes and they have a sign. Note also the discrepancy between the values on the H column, which are both positive and negative, and the ones in the y-axis, which are only positive.

• Hi cholo14, do you mean that you would like to add the label of the y axes (e.g. hours) and for the x axes e.g. (something)? – user88036 Sep 19 '18 at 16:53
• There are a few questions: 1 Is the y axis "time of day" or "time in hours/minutes" ... 2 Did you check superuser ... 3 Can you provide a sample of the spreadsheet data in CSV format, or something tabular in your question? – RubberStamp Sep 19 '18 at 16:59
• I think I see what you mean... Try the following: 1 Subtract the End Time from Start Time ... 2 Subtract your nominal time value ... 3 Format a new answer column as a plain Number format ... 4 Multiply by the time subtracted value by the number of minutes in a 24 hour day ... 1440 ... – RubberStamp Sep 19 '18 at 17:28
• hi @Goro, no, I want the labels on the y-axis to have negative values and not to follow a circular clock algebra. I have updated the question, I hope it is more clear now – cholo14 Sep 20 '18 at 8:02
• @RubberStamp thanks but I want to keep the time in hours, not in minutes, so I cannot use your workaround. 1) the y-axis represents the data on the H column 2) no...would that help? 3) Done, I have updated the question – cholo14 Sep 20 '18 at 8:03

As pointed out by OP in the comments, negative time values can be displayed in LibreOffice by formatting the cell as [HH]:MM.

Time calculations result in a fraction of the amount of time in a day. In order to find the departure from some nominal value, subtract the value from the time difference between End and Start.

I've created a sample spreadsheet in CSV format which includes a statically listed nominal value as well as a calculated average value:

Start,End,Commute Time,,Normalized ΔT,Departure from Average
09:30,10:00,=B2-A2,,=C2-A10,=C2-C10
13:45,14:00,=B3-A3,,=C3-A10,=C3-C10
08:15,09:00,=B4-A4,,=C4-A10,=C4-C10
10:20,11:35,=B5-A5,,=C5-A10,=C5-C10
07:00,08:30,=B6-A6,,=C6-A10,=C6-C10
09:00,09:01,=B7-A7,,=C7-A10,=C7-C10
,,,,,
Static Nominal,,Computed Average,,,
00:20,,=SUM(C2:C7)/6,,,

You can copy and paste the above into a new spreadsheet to see how it works. Make sure you use "Paste Special" option and select comma as the delimiter.

Following are screenshots showing the sample data in graph form...

Copy and Special Paste the CSV Change cell format to Time with [HH]:MM Plot the commute time variances from a static and average values • there are negative time values in LibreOffice-Calc, it is enough to use as format code [HH]:MM. Thanks for your help, but I don't want to convert the time into minutes, I want the plot to show the time in hours with sign – cholo14 Sep 20 '18 at 7:56