Finding difference in time using OpenOffice.org Calc

Question

I have data in this format:

      Action  (A)                  |     Time logged (B)
  ---------------------------------|-----------------------
1 |Policy converted successfully   |   19-FEB-10 18:34:01   
2 |Policy converted successfully   |   19-FEB-10 18:34:10   
3 |Policy converted successfully   |   19-FEB-10 18:34:21   

I need to find the time difference between row 2 and row 1, in seconds ( or minutes/hours). How can I do the same using OpenOffice.org Calc ? I tried =B2-B1, the resulting cell had an Err: 529 as the result. I've tried putting format mask of DD-MMM-YY HH:MM:SS on the source as well as the destination cells, but to no effect. Any hints as to how to accomplish this ?

There are about 2,800 rows of records, going through each is just not possible.

Answer 1

As Chris answered, your problem ist that your worksheet stores the dates as a text string, not as a date.

In addition to that, a cell can only be a date (i.e. a day on the calender), or a time (wallclock time), not both. Edit: Not true, this works just fine. It's just that there's no parsing function to parse date+time in one go.

So first, you need to split your date+time string into two columns. Then use DATEVALUE to convert the date, and TIMEVALUE to convert the time. Then you can calculate with them.

To do this, you can use formulas like this:

Policy converted successfully   |       19-FEB-2010 18:34:01 | =LEFT(B1; 11) | =RIGHT(B1;8) | =DATEVALUE(C1) | =TIMEVALUE(D1) | =E1+F1

Explanation:

• The first two formulas (LEFT, RIGHT) split up the string into date and time, to parse them separately.
• The next two (DATEVALUE,TIMEVALUE) parse a string as a date / a time
• the last combines date+time into one cell (internally date and time are just float numbers, so you can add)

Now you can use them to your heart's content. Of course you could combine the formulas to use less cells.

If the last column does not show up as a date+time, but as a number, choose "date" formatting.

Answer 2

For calculating the difference of two times in hours:

=(HOUR(E17)+(MINUTE(E17)/60))-(HOUR(D17)+(MINUTE(D17)/60))

where D17 is the "from time" and E17 is the "to time". Both E17 and D17 should be formatted as time.

Answer 3

Would something like this help?

http://www.ehow.com/how_5924425_calculate-time-differences-openoffice.html

I don't have OO installed so can't verify. Basically use the DATEDIFF function.

Answer 4

Sounds like the data is a string, and needs to be converted to a date first. Dates are internally stored as floating point numbers, so you can just subtract them and get the difference (the result may still need formatting depending on what you're looking for).

Answer 5

That error means something like that the cell formatting is messed up. I'm unable to reproduce it (3.1, Ubuntu 9.10) (it's just plain working for me) but you should check the 'real' content of the cell, as it shows up in the edit-textbox in the toolbar. Also try to set the Cell Formatting (Right Click -> Format Cells... -> Numbers) to a Date-Type (not just using the mask, set it explicitly to one of the predefined Date-Masks).