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

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