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.
Thanks for your help in advance
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.
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.