Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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 :) –  Sathya Feb 24 '10 at 21:02

5 Answers 5

up vote 4 down vote accepted

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.

share|improve this answer
    
Thanks, this seems promising. I'll try it out and let you know tomorrow, just left office ;) –  Sathya Feb 22 '10 at 23:41
1  
Worked like a charm, thanks a lot! –  Sathya Feb 23 '10 at 12:32

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.

share|improve this answer
    
This is a great, generic answer. –  DanM May 10 '13 at 18:46

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.

share|improve this answer
    
Unfortunately datediff doesn't seem to take in cell numbers as reference :( –  Sathya Feb 22 '10 at 21:51
    
:-O sorry I should have installed OO and test it :) –  Burton Feb 22 '10 at 21:52
    
no worries, thanks for the try :) –  Sathya Feb 22 '10 at 22:08
    
It seems that datediff is not a worksheet function (see wiki.services.openoffice.org/wiki/Documentation/How_Tos/…) but something from the HSQLdb database engine (see wiki.services.openoffice.org/wiki/…) –  Arjan Feb 24 '10 at 10:54

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

share|improve this answer
    
I've applied the date format mask, as mentioned in my question but to no effect. –  Sathya Feb 22 '10 at 22:11

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

share|improve this answer
    
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 –  Sathya 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. –  Sathya 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. –  Sathya Feb 23 '10 at 12:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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