Announcement

Collapse
No announcement yet.

Convert hh:mm:ss to seconds

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Convert hh:mm:ss to seconds

    I have a cell with hh:mm:ss formatted values of say 2:02:48, but need to run some calculations. I want to convert this to the just the number of seconds .. in this case 7368 seconds, but can't see how to do it. Changing the format of the cell to just ss returns 48.

    I'm sure this is really simple and I'm being a bit thick.

  • #2
    Re: Convert hh:mm:ss to seconds

    Excel stores dates and times as numbers, with the integers representing days (since 1 january 1900). The decimal part is the fraction of a day.

    If your cell has only hh:mm:ss with no day part, (Excel will treat it as that time on 1 January 1900), simply multiply the cell by the number of seconds in a day -- 24*60*60)

    EDIT: If your cell might also have a non-zero date component, just to be safe you could use:

    =MOD(A1,1)*24*60*60

    where A1 is assumed to have your orginal hh:mm:ss data.
    Last edited by thomach; May 16th, 2005, 22:54.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

    Comment


    • #3
      Re: Convert hh:mm:ss to seconds

      Thomach,

      Excellent - exactly what was required. Many thanks for the quick response. Thanks too to OzGrid

      Comment


      • #4
        Re: Convert hh:mm:ss to seconds

        Originally posted by dawkinj View Post
        I have a cell with hh:mm:ss formatted values of say 2:02:48, but need to run some calculations. I want to convert this to the just the number of seconds .. in this case 7368 seconds, but can't see how to do it. Changing the format of the cell to just ss returns 48.

        I'm sure this is really simple and I'm being a bit thick.
        The easiest and cleanest way to do this is simply change the cells format to:
        [s]

        placing an indicator inside square brackets removes the limitation to its normal reset value. Ie seconds = 60, minutes = 60, hours = 24.

        the format [h]:mm:ss will stop that anoying display with the number of days if the duration is greater than 24 hours.

        Hope this helps

        Comment

        Working...
        X