Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Convert Hours, Minutes And Seconds Into Total Seconds

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

  • 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?

  • #2
    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

    Comment


    • #3
      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.

      Comment


      • #4
        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.

        Comment


        • #5
          Re: Convert Hours, Minutes And Seconds Into Total Seconds

          Tom, has the best solution, IMO

          K.I.S.S

          Comment


          • #6
            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

            Comment


            • #7
              Re: Convert Hours, Minutes And Seconds Into Total Seconds

              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.

              Comment


              • #8
                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

                Comment


                • #9
                  Re: Convert Hours, Minutes And Seconds Into Total Seconds

                  Originally posted by thomach View Post
                  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.
                  You can use cell formatting for display purposes, but if you want to use this in a downstream formula, you can use a similar approach by using:
                  =TEXT(A1, "[s]")

                  This produces the same result, but converts it into total seconds instead of only displaying it as total seconds.

                  Comment

                  Trending

                  Collapse

                  There are no results that meet this criteria.

                  Working...
                  X