4

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.

3
  • Daylight saving time, anyone? – Arjan Feb 24 '10 at 10:41
  • (And if this is an import of some text file, then explicitly defining date-time columns as date-times while importing might help.) – Arjan Feb 24 '10 at 10:57
  • @Arjan - DST is not applicable here :) – Sathyajith Bhat Feb 24 '10 at 21:02
5

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.

1
  • Thanks, this seems promising. I'll try it out and let you know tomorrow, just left office ;) – Sathyajith Bhat Feb 22 '10 at 23:41
5

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.

2
  • This is a great, generic answer. – DanM7 May 10 '13 at 18:46
  • 1
    You could just do =E17-D17 and use [HH] or [HH]:MM to format the result. – Jason C Apr 22 '15 at 23:33
1

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.

3
1

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

1
  • I've applied the date format mask, as mentioned in my question but to no effect. – Sathyajith Bhat Feb 22 '10 at 22:11
1

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

5
  • This is what it shows in the edit box - 19-FEB-10 18:33:20 Applied only of the predefined masks, but still Err:529 – Sathyajith Bhat Feb 22 '10 at 23:13
  • @Sathya: What version do you have anyway? – Bobby Feb 22 '10 at 23:18
  • I'm on 3.0.0 ( OOO300m9 , build 9358), labelled as Novell edition. I'm using on Windows, not Linux. – Sathyajith Bhat Feb 22 '10 at 23:49
  • @Sathya: Any chance that you can upgrade to a newer version? This might be a bug which got fixed (I somewhere read that this error was related with a bug). – Bobby Feb 23 '10 at 8:03
  • Sorry I cannot upgrade, its beyond my control. – Sathyajith Bhat Feb 23 '10 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.