Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   How do I convert seconds to true time (https://www.askmehelpdesk.com/showthread.php?t=414403)

  • Nov 9, 2009, 04:34 PM
    ecarranco
    How do I convert seconds to true time
    I have a bunch of values that are in seconds 1815632 seconds. Dividing that by 60 and the results of that by 60 I get 504.34 hours. This is close, but I don't want decimal. I need it to tell me it is 504 hrs and XX Minutes and xx seconds. This is the requirement now, but I'm sure it will change to take the seconds and convert it to days/hours/minutes/seconds eventually.

    Thanks you in advance for your assistance.
  • Nov 9, 2009, 05:09 PM
    Synnen

    Multiply the decimal (.34) by 60 to get the number of minutes, then the decimal of THAT again by 60 to get the number of seconds.
  • Nov 9, 2009, 06:06 PM
    JBeaucaire

    In an adjacent cell, enter this formula:

    =A1/86400

    Then apply a custom number format to it like so:

    [h]:mm:ss


    Result: 504:20:32

    Or to get it spelled out, a custom number format of:

    [h] "hours" mm "minutes" ss "seconds"

    Result: 504 hours 20 minutes 32 seconds


    ========
    To include days:

    d:h:mm:ss

    Result: 21:00:20:32

    d "days" h "hours" mm "minutes" ss "seconds"

    Result: 21 days 0 hours 20 minutes 32 seconds
  • Nov 9, 2009, 06:12 PM
    ecarranco
    You folks are awesome! Thanks so very much.

    Solution was.

    =TEXT(A1/86400,CHOOSE(MATCH(A1,{0,60,3600},1),"s ""sec""","m ""min"" s ""sec""","[h] ""hrs"" m ""min"" s ""sec"""))

    Your prompt reply is sooo very much appreciated!

  • All times are GMT -7. The time now is 01:29 AM.