Share via

Convert Seconds into HH:MM:SS format

Anonymous
2010-03-24T22:17:08+00:00

Hi

I have a cell which contains a total amount of seconds. I need this number expressed in terms of hours,minutes,and secondsformat i.e 9084 will be 2:30:59 and if i type any seconds should get the result as HH:MM;SS format. Plz Help

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2010-03-25T00:28:26+00:00

    You can also try

    A1=9084

    B1==TEXT(A1/(24*60*60),"hh:mm:ss")

    Hope this helps you.

    200+ people found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-12-14T16:33:42+00:00

    I know this is an old question but it's still useful and I haven't found a better solution out there.

    Other answers I have seen on this site which use the TIME function will work so long as the number of hours is less than 24, once they are higher then it will fail as it's actually using a date format and just showing the time portion of it (and date formats cannot have more than 23 hours in a day). E.g. 86401 seconds (a day plus one second) will show "00:00:01".

    The way around it is to use a bit of maths (floor and mod) and build the hours, minutes and seconds up with concatenation:

    A1=86401

    A2==CONCATENATE(TEXT(FLOOR.MATH(A1/60/60),"00"),":",TEXT(FLOOR.MATH(MOD(A1,60*60)/60),"00"),":",TEXT(FLOOR.MATH(MOD(A1,60)),"00"))

    This will give a value of "24:00:01" which is what I needed.

    Note, I'm purposely formatting the H, M and S values with two digits, but if the number of hours is larger than 99 then it will expand to three or more figures.

    Hope someone else finds that useful,

    Andy

    40+ people found this answer helpful.
    0 comments
  2. Anonymous
    2010-03-24T23:02:08+00:00

    Two points:

    1. 9,084 seconds is actually 2 hours 31 minutes and 24 seconds
    2. Excel sees time as decimal fractions of a day....eg Noon = 0.5.  Consequently, Excel will treat 9,084 as a date and return: November 13, 1924.  Additionally, without a VBA macro or manual intervention, there's no way for Excel to assume you meant seconds and convert that number to: 0.105138888888889 (the decimal representation of 9,084 seconds as a fraction of a day:

    = 9,084 seconds

    = 9,084 seconds / (60 seconds per minute * 60 minutes per hour * 24 hours per day)

    = 9,084/(60*60*24)

    = 9,084/86400

    = 0.105138888888889

    That value, formatted as time, displays as 2:31:24

    Consequently, with:

    A1: 9084

    you could use one of these approaches (formatted as time)

    B1: =A1/(60*60*24)

    or

    B1: =TIME(,,A1)

    Is that something you can work with?

    Ron Coderre

    Microsoft MVP - Excel (2006 - 2010)

    P.S. If any post answers your question, please mark it as the Answer

    (That way it won't keep showing as an open item.)

    10+ people found this answer helpful.
    0 comments
  3. Anonymous
    2015-05-21T19:14:14+00:00

    Oh my lord,

    It was that simply!? =TIME(,,value)

    Thank you sir, you have saved my day.

    10 people found this answer helpful.
    0 comments
  4. Anonymous
    2015-09-15T13:39:35+00:00

    Yeah.  Way to make me feel dumb.  Only been looking for this answer since 2012.

    0 comments