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 several cells with a time value in seconds e.g (23, 102, 543, ...)

I am not very familiar with the formula functions in Open Office. How do I address the same Cell-Box with the value in it? I want to replace all the second-values in that cell with a minute format (102seconds = 1:42).

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

In a spare row or column of your spreadsheet that lines up with the existing data, enter the following formula:

=TIME(0;0;A1)

Where A1 is the cell with the time in seconds. That function takes hours, minutes, and seconds as parameters and converts it into OpenOffice's regular time format.

Then, copy or drag to autofill the rest of the cells that are necessary to perform the calculation on all the relevant data. Now, highlight all the newly calculated values, right-click and choose Cut or press CTRL+X, then highlight the old values and right-click and choose Paste Special. Uncheck Paste all and then uncheck everything but Date & time and click okay.

Finally, right click on the whole set of cells, choose Format Cells, select Time under Category and select whichever format you desire.

share|improve this answer
    
thx, this worked great, only that I had to use =TIME(0;0;A1) ;D –  Marcel Feb 14 '11 at 17:07
    
Oops, sorry about that! I'll fix my answer for future readers. –  Patches Feb 14 '11 at 22:49
add comment

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.