Ask Your Question

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

asked 2016-02-17 10:25:33 +0200

bonanza123 gravatar image


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.

Thanks for your help in advance

edit retag flag offensive 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.

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

1 Answer

Sort by » oldest newest most voted

answered 2017-04-01 23:56:24 +0200

Regina gravatar image

updated 2017-04-02 00:16:20 +0200

Lupp gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-02-17 10:24:22 +0200

Seen: 474 times

Last updated: Apr 02 '17