Results 1 to 7 of 7

Thread: Seconds to days, hours, minutes

  1. #1
    Seeker randz77's Avatar
    Join Date
    Oct 2015
    Location
    Philippines
    Posts
    10
    Articles
    0
    Excel Version
    2013

    Seconds to days, hours, minutes



    Register for a FREE account, and/
    or Log in to avoid these ads!

    So I have 3401388 seconds in cell A1, I'm trying to convert it to dd:mm:ss using the formula text(A1/86400, "dd hh:mm:ss") but its only giving me 8 days, 08:49:48, when it should be around 39 days.. Not sure if I'm missing something

    Sent from my INE-LX2 using Tapatalk

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    147
    Articles
    0
    Excel Version
    2019
    Here is how I would calculate

    Data Range
    A
    B
    C
    D
    E
    F
    G
    1
    Minutes
    Hours
    Days
    Hours
    Minutes
    Seconds
    2
    3401388
    =A2/60
    =B2/60
    =C2/24
    =(D2-INT(D2))*24
    =(E2-INT(E2))*60
    =(F2-INT(F2))*60


    and then concatenate the integer portions of D, E, F and G appropriately.

  3. #3
    Seeker randz77's Avatar
    Join Date
    Oct 2015
    Location
    Philippines
    Posts
    10
    Articles
    0
    Excel Version
    2013
    Thanks alansidman. The formula works in other cells. not sure why it's not working consistently even after restarting excel.


    Quote Originally Posted by alansidman View Post
    Here is how I would calculate

    Data Range
    A
    B
    C
    D
    E
    F
    G
    1
    Minutes
    Hours
    Days
    Hours
    Minutes
    Seconds
    2
    3401388
    =A2/60
    =B2/60
    =C2/24
    =(D2-INT(D2))*24
    =(E2-INT(E2))*60
    =(F2-INT(F2))*60


    and then concatenate the integer portions of D, E, F and G appropriately.

  4. #4
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    147
    Articles
    0
    Excel Version
    2019
    We cannot test and manipulate a picture. If you will upload your workbook then I am sure that a solution can be found after some testing.

  5. #5
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,649
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Also please do not quote entire posts unnecessarily.
    Use the "Quick Reply" instead
    Thank you Ken for this secure forum.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,537
    Articles
    0
    Excel Version
    365
    Try:
    Code:
    =INT(A1/86400) & " days " & TEXT(MOD(A1/86400,1),"hh:mm:ss")

  7. #7
    Seeker randz77's Avatar
    Join Date
    Oct 2015
    Location
    Philippines
    Posts
    10
    Articles
    0
    Excel Version
    2013
    this thing works.. thanks p45cal

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •