# How to convert floating point value into decimal value in open office?

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

-
 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

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`

-
 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

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.

-
 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