Finding difference in time using OpenOffice.org Calc

17,176

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

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

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

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

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

View more solutions

17,176

100rabh

Updated on September 17, 2022

Comments

100rabh over 1 year
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.
- Arjan about 14 years
  
  Daylight saving time, anyone?
- Arjan about 14 years
  
  (And if this is an import of some text file, then explicitly defining date-time columns as date-times while importing might help.)
- 100rabh about 14 years
  
  @Arjan - DST is not applicable here :)
100rabh about 14 years

Unfortunately datediff doesn't seem to take in cell numbers as reference :(
Misha about 14 years

:-O sorry I should have installed OO and test it :)
100rabh about 14 years

I've applied the date format mask, as mentioned in my question but to no effect.
100rabh about 14 years

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
Jjames about 14 years

@Sathya: What version do you have anyway?
100rabh about 14 years

Thanks, this seems promising. I'll try it out and let you know tomorrow, just left office ;)
100rabh about 14 years

I'm on 3.0.0 ( OOO300m9 , build 9358), labelled as Novell edition. I'm using on Windows, not Linux.
Jjames about 14 years

@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).
Arjan about 14 years

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/…)
Jason C almost 9 years

You could just do =E17-D17 and use [HH] or [HH]:MM to format the result.