Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: formula to convert minutes and seconds into just seconds

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    at work i have to input time for the average
    lenght of the call and then convert into seconds.
    is there a formula to do so
    like a1 is 3:15
    in b1 i want a formula to convert the 3:15 into just seconds which would be 195

    thanks

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If cell A1 contains a time value formatted as m:ss then enter the following formula in cell B1...

    =A1*86400

    If cell A1 contains "3:15" use...

    =TIMEVALUE("0:"&A1)*86400

    [ This Message was edited by: Mark W. on 2002-04-23 11:03 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks
    just what i was looking for
    how did u know that formula

    chris

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    also do u know what the formula is to convert back into minutes and seconds

    thanks
    chris

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-23 11:08, chriscim wrote:
    thanks
    just what i was looking for
    how did u know that formula

    chris
    Which formula did you use?

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    the second one

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-23 11:11, chriscim wrote:
    also do u know what the formula is to convert back into minutes and seconds

    thanks
    chris
    =TEXT(B1/86400,"m:ss")

    where B1 contains the seconds as an integer.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-23 11:08, chriscim wrote:
    thanks
    just what i was looking for
    how did u know that formula

    chris
    Excel stores time values as a fractional part of a day... there are 86,400 seconds in a day. Excel time values are entered as hours:minutes or hours:minutes:seconds so...

    =TIMEVALUE("0:"&A1)*86400

    ...first converts your text string to a time value and then converts that fraction of a day into seconds.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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