Results 1 to 9 of 9

Thread: Need help formatting time.

  1. #1

    Need help formatting time.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Guys,

    I am new to excel..

    I got cells with text entries like "1 Min 30 Secs" or "36 Secs". Is there a way where excel can convert 1 Min 30 Secs to 90 seconds? The name of the row is Duration, and I got around 50,000 such rows to convert.

    Here is what my sheet looks like. Any help is appreciated.

    Duration
    5 Secs
    6 Secs
    13 Secs
    14 Secs
    6 Secs
    24 Secs
    24 Secs
    3 Secs
    1 Min 58 Secs
    4 Secs
    12 Secs
    17 Secs
    11 Secs

    Cheers
    Shri.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Assuming the data is in column A, starting at A2, then in B2 enter formula:

    =IF(ISNUMBER(SEARCH("Min",A2)),LEFT(A2,SEARCH("Min",A2)-1)*60+SUBSTITUTE(MID(A2,SEARCH("Min",A2)+4,255),"Secs","")&" Secs",A2)

    copied down.

    You can then copy and paste special >> Values over the original if desired and remove the formula column.


  3. #3
    Thank you a Ton NBVC,.. it worked like charm. The out put prints with Secs attributes, i.e 1 Min 48 Secs is calculated as 108 Secs. Any idea if it can just give 108 instead of 108 secs? Also, what if I have some entries like 2 hrs 30 mins 20 secs?



  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    So you just want total seconds for all cells without the "Secs" at the end?

    Also, you should always list a representative sampling of all possibilities... adding hours adds complexity.


  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Also will there be a possibility of an entry like: 5 Hrs 0 Mins 10 Secs, or 2 Min, or 5 Hr 7 min? (i.e. will the Mins or Secs ever be "missing" if they are 0?)


  6. #6
    It will be missing. The data I am feeding is a constant variable.. i.e, I may get 5 hrs 2 Mins 3 secs, or 1 Min 58 Secs, or just Secs. I may also get 5 hrs 4 Secs. !!!.. Sounds weird.

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Hopefully this will work...

    =IFERROR(MID(" "&SUBSTITUTE(A2," "," "),SEARCH("Hr"," "&SUBSTITUTE(A2," "," "))-10,5)*3600,0)+IFERROR(MID(" "&SUBSTITUTE(A2," "," "),SEARCH("Min"," "&SUBSTITUTE(A2," "," "))-10,5)*60,0)+IFERROR(MID(" "&SUBSTITUTE(A2," "," "),SEARCH("Sec"," "&SUBSTITUTE(A2," "," "))-10,5)+0,0)

    copied down


  8. #8
    I just got the actual data. Here are 3 combinations for converting to Secs.

    Combination 1 : 7 Secs
    Combination 2: 3 Mins 1 Secs

    *=IF(ISNUMBER(SEARCH("Min",A2)),LEFT(A2,SEARCH("Min",A2)-1)*60+SUBSTITUTE(MID(A2,SEARCH("Min",A2)+4,255),"Secs","")&" Secs",A2)

    The above formula works just fine for combination 1 and 2.
    ===============================================================

    Combination 3: 1 Hr 30 Mins
    I changed my formula like below and fails to convert 1 Hr 30 Mins to Secs.

    =IFERROR(MID(" "&SUBSTITUTE(H2," "," "),SEARCH("Hr"," "&SUBSTITUTE(H2," "," "))-10,5)*3600,0)+IFERROR(MID(" "&SUBSTITUTE(H2," "," "),SEARCH("Mins"," "&SUBSTITUTE(H2," "," "))-10,5)*60,0)+IFERROR(MID(" "&SUBSTITUTE(H2," "," "),SEARCH("Secs"," "&SUBSTITUTE(H2," "," "))-10,5)+0,0)


    Sorry guys, I am new to excel and not sure what I miss.

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    I didn't notice, but I think the forum software trimmed all my spaces.

    Let's try this:

    Code:
    =IFERROR(MID("     "&SUBSTITUTE(A2," ","     "),SEARCH("Hr","     "&SUBSTITUTE(A2," ","     "))-10,5)*3600,0)+IFERROR(MID("     "&SUBSTITUTE(A2," ","     "),SEARCH("Min"," "&"     "&SUBSTITUTE(A2," ","     "))-10,5)*60,0)+IFERROR(MID("     "&SUBSTITUTE(A2," ","     "),SEARCH("Sec"," "&"     "&SUBSTITUTE(A2," ","     "))-10,5)+0,0)


Posting Permissions

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