Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Convert Hours, Minutes And Seconds Into Total Seconds

  1. #1
    Join Date
    10th April 2008
    Posts
    7

    Convert Hours, Minutes And Seconds Into Total Seconds

    Hi All,

    Am stuck trying to figure a formula to convert time on a phone call eg. 01:01:21 into total seconds (3661). Phonecalls will never be more than an hour long but the spreadsheet I will be supplied with (havn't got it yet!) will display them in the 00:00:00 format.

    Any Ideas?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,651

    Re: Convert Hours, Minutes And Seconds Into Total Seconds

    Assuming the time is in B3

    Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
     
     
    =HOUR(B3)*3600+MINUTE(B3)*60+SECOND(B3)
     

    Triumph without peril brings no glory: Just try

  3. #3
    Join Date
    2nd November 2005
    Location
    Tecumseh, OK
    Posts
    1,092

    Re: Convert Hours, Minutes And Seconds Into Total Seconds

    If A1=01:01:21 then =A1*86400 is 3681 seconds. Be sure to format the formula cell as Numeric. The 86400 comes from 3600 seconds/hour times 24 hours per day. Time is in a fraction of a day.

    Excel Video Tutorials / Excel Dashboards Reports


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

    Re: Convert Hours, Minutes And Seconds Into Total Seconds

    You also could format the cell as [s]

    FORMAT > CELLS > CUSTOM > enter [s] in the Type box.

    Note: This leaves the underlying data as a fraction of a day, but displays the stored fraction in seconds. If you need the number of seconds to be stored so you can do math with it, the convert as above or incorport the conversion formulas into your math.
    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.

  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

    Re: Convert Hours, Minutes And Seconds Into Total Seconds

    Tom, has the best solution, IMO

    K.I.S.S

  6. #6
    Join Date
    10th April 2008
    Posts
    7

    Re: Convert Hours, Minutes And Seconds Into Total Seconds

    thanks millions guys, I can get all methods working so will have to wait and see when the data comes through which is best.

    Thanks again

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    27th April 2012
    Posts
    1

    Re: Convert Hours, Minutes And Seconds Into Total Seconds

    Quote Originally Posted by Dave Hawley View Post
    Tom, has the best solution, IMO

    K.I.S.S
    I'm having problems with this.

    Tom's may be the 'best solution' but only for display purposes (I think). Don't try to run downstream formula's, because the results will not be correct.

    For example: 2 hours, 43 minutes and 31 seconds (2:43:31) is a total of 9811 seconds. Tom's formatting shortcut will display the correct number in the cell, but that's it.

    Do it the long way, and place your answer in A1. Put the formatted shortcut in A2. Then apply this formula in cell A3:

    =IF(A1=B1,"Y","N")

    The result in A3 will be N.

    If this is applied to a list, then the =SUM function is used, the total will not be accurate, unless I'm doing something wrong.

    I'm in Excel 2007, so help a brother out if I'm wrong.

    Thx.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,651

    Re: Convert Hours, Minutes And Seconds Into Total Seconds

    The unit for time is linked to a number of day
    The value for 2:43:31 is 0.113553240740741
    It means 0.113553240740741 * 24 * 3600 = 9811 seconds
    Triumph without peril brings no glory: Just try

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Convert Seconds Into Hours, Minutes & Seconds
    By chris_g in forum EXCEL HELP
    Replies: 7
    Last Post: December 7th, 2007, 22:13
  2. Replies: 4
    Last Post: September 25th, 2007, 01:16
  3. Replies: 5
    Last Post: December 5th, 2006, 07:41
  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