OpenOffice Calc: Graph ON/OFF times

Question

I have a simple spreadsheet with every row containing times (24h) something was switched on and off again. So it looks something like this:

10:20:15    10:25:36
11:09:02    11:15:54
13:00:38    13:01:15
16:35:44    16:48:31

So something was switched on at 10:20AM and switched off again at 10:25AM. Then switched on again at 11:09AM, and switched off again at 11:15AM.

I want to show this in a (daily) graph, with the x-axis displaying 24 hours. Something like this:

Is this something that can be done in OpenOffice automatically? Or through a script? Or another program (offline or online)?

Answer 1

It looks like you want variable width bars that start and stop according to the times in the data. One way to do that is with an XY chart:

You can make the appearance more like your example if that's needed:

• Hide the Y axis labels and use the axis title or text boxes to label "On" and "Off".
• Manually set the X axis minimum and maximum times and the grid intervals. You could also convert the time values to hours and plot hours for the X axis (note that minutes need to be converted to decimal fractions of an hour, so use a method like multiplying the stored time value by 24).
• If you want the bars filled, you could manually add colored rectangles.

How it works

Formulas create 0 and 1 values for each start time and 1 and 0 values for each stop time. The graph is an XY chart with lines but no points.

The times list is created with this formula in D1:

=OFFSET($A$1,CEILING(ROW()/4)-1,ROUND(MOD(ROW()-1,4)/4,0))

This starts with the first value and then offsets by row and column to grab the right time and create two values (for 0 and 1) for each.

The 0 and 1 bar values are created with this formula in E1:

=MOD(ROUND(MOD(ROW()-1,4)/4,0)+1-MOD(ROW(),2),2)

Note that I kept this simple to focus on the approach. If your data doesn't start in row 1, you will need to adjust the row-based formulas. Also, be careful to populate the formula only to 4 times the number of data rows. Beyond that, the formula will pickup times of "zero" or midnight. If you want to get fancy, you can build in an error check.