View original (French)

Conversion of a number to seconds

Solved
Bingo_amd -  
Hello,

I would like to convert a column containing numbers in seconds as shown in the following example;

234 = 00:02:34 = 154 seconds

Thank you.

4 answers

  1.  eriiic Contributor
    Hello,

    one more! one more!
    Alright, here it is: 
    =TEXT(A2;"00\:00\:00")*86400

    eric

    --
    By continually trying, we eventually succeed.
    So the more it fails, the more chances we have that it works. (the Shadoks)
    In addition to the thank you (yes, yes, it happens!!!), remember to put it in resolved. Thank you
    4
    1. marc
      Well done eriiic, hats off!!! You've found the shortest and simplest formula! :)
      Addition: it's funny when you imitate Coluche! ;P
      0
  2. Bingo_amd Author
    It's working perfectly :)

    Thank you all Vaucluse & eriiic :)
    0
    1. Bingo_amd Author
      234 represents 2 minutes and 34 seconds;

      another example: 10420 represents: 1 hour 4 minutes and 20 seconds

      the source file that I receive this way

      Thank you.
      0
      1. Vaucluse Contributor
        So try this one for A1, provided that each item hh:mm:ss is represented by two digits (except for the first one which can have only one)
        =IFERROR((LEFT(A1,LEN(A1)-4)*3600),0)+IFERROR(LEFT(RIGHT(A1,4),MIN(LEN(A1)-2,2)),0)*60+RIGHT(A1,2)

        best regards
        0
      2. marc
         
        Hello Bingo_amd,

        I suggest this formula (to be put on one line):

        =LEFT(TEXT(A1;"000000");2)*3600+MID(TEXT(A1;"000000");3;2)*60
        +RIGHT(A1;2)

        10420 in A1 => 3860 seconds
        234 in A1 => 154 seconds
        0
    2. Vaucluse Contributor
      Hello

      234 transformed into seconds gives for Excel 00:03:54
      (that is, 3 minutes = 180 + 54 seconds = 234
      formula: > =A1/86400

      and apparently this is not what you are looking for!

      So, please tell us exactly what 234 represents and what are the variations of this value that you may need to handle?
      looking forward to hearing from you

      The quality of the response largely depends on the clarity of the question, thank you!
      2