+ Reply to Thread
Results 1 to 11 of 11

Converting "hh h mm m ss s" time format to "hh:mm:ss"

  1. 09-28-2013, 02:51 AM #1
    sohel.engr
    sohel.engr is offline
    Registered User
    Join Date
    09-28-2013
    Location
    Dhaka
    MS-Off Ver
    Excel 2007
    Posts
    8

    Converting "hh h mm m ss s" time format to "hh:mm:ss"

    Hi,
    Hope you guys can help me.

    I have the following data:


    222 h18 m 56 s
    115h 3m 1s

    Is there a way to convert it to:

    222:18:56
    115:03:01


    Thanks
    Sohel
    Reply With QuoteRegister To Reply
  2. 09-28-2013, 05:03 AM #2
    Philb1
    Philb1 is offline
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Converting "hh h mm m ss s" time format to "hh:mm:ss"

    Hi Sohel
    You can use the replace function. If the cells always have the same spacing maybe something like this
    Please Login or Register  to view this content.
    Or use the built in replace function on the Home ribbon
    Last edited by Philb1; 09-28-2013 at 05:07 AM.
    Reply With QuoteRegister To Reply
  3. 09-28-2013, 05:30 AM #3
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Converting "hh h mm m ss s" time format to "hh:mm:ss"

    Try this...

    In A1 Cell
    222 h18 m 56 s

    In B1 Cell

    =IFERROR(TRIM(LEFT(A1,SEARCH("H",A1)-1))&":"&TEXT(TRIM(MID(A1,SEARCH("H",A1)+1,SEARCH("M",A1)-SEARCH("H",A1)-1)),"00")&":"&TEXT(TRIM(SUBSTITUTE(MID(A1,SEARCH("M",A1)+1,255),"s","")),"00"),"")


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  4. 09-28-2013, 05:39 AM #4
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Converting "hh h mm m ss s" time format to "hh:mm:ss"

    welcome to the forum, soheLengr. another alternative:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"h",":"),"m",":"),"s","")+0

    format cells to custom:
    [hh]:mm:ss

    assumption is you always have h, m & s.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
  5. 09-28-2013, 07:22 AM #5
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Converting "hh h mm m ss s" time format to "hh:mm:ss"

    Welcome to the Forum!

    Because your first post is an Excel question rather than an introduction, I have moved it to the appropriate forum. Edit: Never mind, someone beat me to it!
    Reply With QuoteRegister To Reply
  6. 09-28-2013, 07:37 AM #6
    sohel.engr
    sohel.engr is offline
    Registered User
    Join Date
    09-28-2013
    Location
    Dhaka
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Converting "hh h mm m ss s" time format to "hh:mm:ss"

    Thanks for your reply.
    Reply With QuoteRegister To Reply
  7. 09-28-2013, 07:52 AM #7
    sohel.engr
    sohel.engr is offline
    Registered User
    Join Date
    09-28-2013
    Location
    Dhaka
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Converting "hh h mm m ss s" time format to "hh:mm:ss"

    it is work nice,

    how convert 234 to 234:00:00(hh:mm:ss)
    Reply With QuoteRegister To Reply
  8. 09-28-2013, 08:09 AM #8
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Converting "hh h mm m ss s" time format to "hh:mm:ss"

    Quote Originally Posted by sohel.engr View Post
    how convert 234 to 234:00:00(hh:mm:ss)
    Not sure to whom it refers to???
    Reply With QuoteRegister To Reply
  9. 09-28-2013, 08:26 AM #9
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Converting "hh h mm m ss s" time format to "hh:mm:ss"

    Quote Originally Posted by sohel.engr View Post
    it is work nice,

    how convert 234 to 234:00:00(hh:mm:ss)
    Try this:

    =A1&":"&"00"&":"&"00"
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  10. 09-28-2013, 12:10 PM #10
    sohel.engr
    sohel.engr is offline
    Registered User
    Join Date
    09-28-2013
    Location
    Dhaka
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Converting "hh h mm m ss s" time format to "hh:mm:ss"

    how count B2 equal or less then 22 where A1 any value


    A1 B2
    25 22

    A1 B2
    22 22

    A1 B2
    18 18
    Reply With QuoteRegister To Reply
  11. 09-28-2013, 03:15 PM #11
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Converting "hh h mm m ss s" time format to "hh:mm:ss"

    What is it that you are trying to do? You have added 2 questions to the original and the last one doesn't even seem to relate to the first 2 in any way.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Date format of just "st" "nd" "rd" and "th" with text included
    By notrandom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2013, 05:45 PM
  2. [SOLVED] Assign Categories for Time of Day: "Morning", "Afternoon", "Evening"
    By long_shanks in forum Excel General
    Replies: 3
    Last Post: 06-11-2013, 02:59 AM
  3. Converting data that is showing as "General" to a custom "Time" format
    By scarames in forum Excel General
    Replies: 2
    Last Post: 11-01-2012, 04:35 PM
  4. [SOLVED] Excel 2007 : Converting "mm min ss s" time format to "hh:mm:ss"
    By blackmilk in forum Excel General
    Replies: 15
    Last Post: 04-23-2012, 04:14 PM
  5. Converting / translating / representing "multiple numbers" as "single unit" ? Table ?
    By BaLLZaCH in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-21-2008, 08:22 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1