Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Convert hh:mm:ss to seconds

  1. #1
    Join Date
    11th March 2005
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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 at 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.

  3. #3
    Join Date
    11th March 2005
    Posts
    4

    Re: Convert hh:mm:ss to seconds

    Thomach,

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    27th September 2013
    Posts
    1

    Re: Convert hh:mm:ss to seconds

    Quote 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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 3 users browsing this thread. (0 members and 3 guests)

Possible Answers

  1. Replies: 7
    Last Post: April 27th, 2012, 20:04
  2. Convert Seconds Into Hours, Minutes & Seconds
    By chris_g in forum EXCEL HELP
    Replies: 7
    Last Post: December 7th, 2007, 22:13
  3. Replies: 4
    Last Post: September 25th, 2007, 01:16
  4. hours:minutes:seconds convert to seconds
    By Pedronis in forum EXCEL HELP
    Replies: 9
    Last Post: July 13th, 2006, 22:12
  5. Convert Seconds into Minutes & Seconds
    By kylewc in forum EXCEL HELP
    Replies: 2
    Last Post: March 30th, 2006, 03:23

Bookmarks

Posting Permissions

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