Convert "days:hours:minutes:seconds" to seconds

Hello,

I have a cell entries which look like "7:20:45:53" and stands for "days:hours:minutes:seconds". How can I convert these to seconds?

I need this because I have other cell entries that are like "92643.665931" in seconds.

edit retag close merge delete

To separate the days-part of a date-time-stamp from the rest by a colon is completely against any reasonable standards and can easily cause misunderstandings.
The "built-in" unit of time in spreadsheets is the day. Since one day consists of 246060 seconds we have the equation 1 d = 86400 s. Conversion from one unit to the other is done now as with any pair of units applicable to a specific kind of quantities.

( 2017-04-02 00:20:45 +0200 )edit

Sort by » oldest newest most voted

Replace the last colon with the decimal separator of you local. Then you can use TIMEVALUE to get the internal number of the time, which is a decimal number in unit "day". Then multiply by 24*60*60 (edited for avoiding misunderstandings; lupp) to get the seconds.

Example: I assume, that the text 7:20:45:53 is in cell A1.
You need =TIMEVALUE(REPLACE(A1;FIND(":";A1;7) ;1;","))*24*60*60

Herein is "," the decimal separator for me in Germany.

It assumes, that the hours exists, so that the position of the third colon is greater than 7.

more