Hello, I have read answers to similar questions, but I have not understood which formula I have to use.
In my case I have in the cell “B2” a time expressed in MM:SS and I need to convert it into seconds.
Just multiply this value with 24 (hours in day) and with 60 (minutes in hour) and 60 (seconds in minute)
=A2*24*60*60
or simple
=A2*86400
Сalс stays the date-time as a number: the integer part denotes the number of days that have passed since the start date, and the fractional part indicates the part of the day that passed since midnight. The usual multiplication of this fractional part by 24 will give you time in hours, multiplying by 24 * 60 will give you time in minutes, and so on.
Thank you, and also to the user Lupp. It works in someway, but not as I would expect. The cell is formatted as time “MM:SS”. When I click on it, the value in the cell turns from 00:00" into “00.00.00”. If I type “60:00” it is converted automatically into “01:00:00”, but I can see it only if I double click on it. Otherwise it shows “00:00”, although it contains 1 hour value.
MM:SS is a wall clock time format where MM displays minutes from 0 to 59 and for 60 wraps around to 0. To display duration in minutes use [MM]:SS
1 Like
This is about the actual conversion into a time result based on the unit s (second).
The answer depends on whether the “time” shown in B2 is text (a string of characters) or a number formatted for display with one of the format codes MM:SS.
In the first case the formula to apply is =TIMEVALUE("00:"&B2)*86400, in the second case it’s =MOD(ROUND(B2*86400;0);1440) which will suppress any number of full hours possibly contained in the value of B2 but not displayed. (Number formats are often lying!)
If you can assure that B2 contains a number displayed in MM:SS format without suppressing any part of it for the display you can use the simpler formula =B2*86400 .
As far as I can see there is no simple way to do the conversion in situ.
If you want to stick to the default unit for time in spreadsheets which is the day…
…you can use in the numeric case the formula =MOD(B2*86400;1440)/86400 and display the result using the format code [SS]
This was recently tested with LibO V5.4.0.1RC Calc. Older versions may handle the usage of square brackets in format codes for minutes and seconds differently (reject it e.g.).
(Editing with respect to comments by the OQer:)
There is no time format for input that allows to omit the hours. If you type 60:00 this is interpreted as 60:00:00 (60 hours, no minutes no seconds). If the cell has the ‘Numbers’ format coded by MM:SS it can only display a maximum time of 59 minutes and 59 (+ fraction) seconds. Simple time formats suppress the overflow. Therefore you get shown 00:00. This may be silly, but it is as spreadsheets do it. During editing the full hour is shown to avoid at least the worst misunderstandings.
With recent versions of LibO you can order the cell to show the overflow in minutes. Try the code [MM]:SS.
You may also use [SS]to get the time shown in seconds including the overflow.
However, all this formatting only afflicts the display. It does not change the time unit of 1 day used by the actual numeric value of the cell.
There are a lot of funny effects. But you mainly should keep in mind that the actual conversion of a time to the unit s (second) requires to multiply the value kept in the defaul unit day by 86400 (=24*60*60) as @JohnSUN already stated.
Thank you I am doing some tests with your suggestions, using the formula =TIMEVALUE(“00:”&B2)*86400 and formatting the cell as text, but I get ERR:502.
It could be useful to extract data from a field which could have a quantity of minutes bigger than 60, e.g. 95:05
For Err:502 check what ="00:"&B2 actually results in.
Also note that time values are fraction of day (with 1 is one day and 0.5 is 12 hours), so multiplying a time value with 86400 is not what you expect it to be.