Tell me more ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.
up vote 0 down vote favorite

In spreadsheet i have more than 5 columns, every columns showing its own value. One of the column shows the talk time as follows,

60:00       
06:44  
08:48     
59:13

I just want to round up this values, as follows.

60     
07       
09     
59

For example 01:25 If the talktime is equal to & above 25seconds it should be rounded as 02 but if the talktime is 01:13 then it should round it as 01 and not as 02.

share|improve this question
Normally, I'd expect to round up after 30 seconds. It sounds like you're trying to give folks a 25 second grace period. If that's the case you're definitely going to need a formula, as suggested by @MestreLion – Amanda Jun 8 '11 at 13:46
@Amanda: he is going to need a formula anyway, since he wants to extraxt just the minutes, and MM alone is not a valid format (it gives me weird results). Must be either HH:MM or MM:SS. Also, HH:MM does not round up. So 00:06:59 becomes 00:06, not 00:07 – MestreLion Jun 8 '11 at 14:04

2 Answers

up vote 1 down vote accepted

I tried to fiddle with Format -> Cell with no sucess. The problem is OpenOffice treats XX:YY values as hours and minutes, NOT minutes and seconds. But you could try this:

  • Input your times with HH:MM:SS format. Like 00:08:48. Say that is A1 cell.

  • Add an aditional column, and input thiis formula in (say B1):

    =minute(A1)+if(second(A1)>=25,1,0)

  • Or, if you dont like adding "00:" to all values, and dont mind OO interpreting it as HH:MM, use this formula instead:

    =hour(A1)+if(minute(A1)>=25,1,0)

  • If you dont like using 2 columns for 1 value (1 for "raw", real value and the other for the formmated value), just right-click the undesirable column and select Hide

share|improve this answer
When I change the format to simply "MM" it doesn't round off for me--I wind up with six minutes in the second row where it should be rounding up to seven: 00:06 00:06 00:08 00:59 – Amanda Jun 8 '11 at 13:43
As far as I can know, minutes (or hours) are never automatically rounded up. Using format strings alone, with no formulas, will always simply truncate the seconds (or minutes) – MestreLion Jun 8 '11 at 14:07
up vote 1 down vote

If the values are all in place already and it is too late to input the times as HH:MM:SS, here's what did work for me:

=IF(VALUE(RIGHT(TRIM(A1),2))>25,SUM(LEFT(TRIM(A1),2)+1),LEFT(TRIM(A1),2))

Crazy, right? I cut and pasted the values, which had a lot of trailing spaces, so I needed "TRIM()" but this says that if the number composed of the two rightmost characters is greater than 25, show the minutes. Otherwise, add 1.

share|improve this answer
I dont see the point in fiddling with VALUE, RIGHT, LEFT and all this manual (and error-prone) parsing when the MINUTE/SECOND functions will do that for you. – MestreLion Jun 8 '11 at 14:40
It depends on whether Calc knows it is looking at minutes and seconds. – Amanda Jun 8 '11 at 16:11
it will know if you input data as 00:06:45 – MestreLion Jun 8 '11 at 16:25
That's true. So if it isn't too late to input the data in HH:MM:SS, that's the other option. – Amanda Jun 8 '11 at 21:43

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.