View original (French)

Conversion of a number to seconds

Solved
Bingo_amd Posted messages 35 Status Member -  
Bingo_amd Posted messages 35 Status Member -
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 Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    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. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    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
  3. Bingo_amd Posted messages 35 Status Member
     
    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 Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      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
  4. Bingo_amd Posted messages 35 Status Member
     
    It's working perfectly :)

    Thank you all Vaucluse & eriiic :)
    0