+ Reply to Thread
Results 1 to 6 of 6

Converting from Text to Time

  1. 06-10-2015, 02:14 AM #1
    KRISHLAL
    KRISHLAL is offline
    Registered User
    Join Date
    09-27-2011
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    9

    Lightbulb Converting from Text to Time

    Hi there, i tried this but then encounted further issues :-(.

    Now the column that i am looking at has following values in texts:

    10 Hours 48 Minutes 11 Seconds
    10 Minutes 12 Seconds
    55 Seconds

    Your formular works for the first one but not the last two

    So i want the following output
    10:48:11
    00:10:12
    00:00:55

    Any help be appreciated.

    krish
    Reply With QuoteRegister To Reply
  2. 06-10-2015, 02:45 AM #2
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,410

    Re: Converting from Text to Time

    One way.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Row\Col
    A
    B
    C
    1
    		 **10 Hours 48 Minutes 11 Seconds 10:48:11 B1 :=TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"Hours",""),"Minutes",""),"Seconds","")," ",""),"00\:00\:00")
    2
    		 **10 Minutes 12 Seconds 00:10:12
    3
    		 **55 Seconds 00:00:55
    Last edited by FlameRetired; 06-10-2015 at 02:55 AM.
    Reply With QuoteRegister To Reply
  3. 06-10-2015, 03:40 AM #3
    KRISHLAL
    KRISHLAL is offline
    Registered User
    Join Date
    09-27-2011
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Converting from Text to Time

    Getting very close hehe, worked for all records except this

    13 Hours 2 Minutes 48 Seconds

    where i got

    01:32:48
    Reply With QuoteRegister To Reply
  4. 06-10-2015, 05:13 AM #4
    cbatrody
    cbatrody is offline
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Converting from Text to Time

    Try the following formula in B1:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 06-10-2015, 10:37 PM #5
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2404
    Posts
    13,410

    Re: Converting from Text to Time

    Quote Originally Posted by KRISHLAL View Post
    Getting very close hehe, worked for all records except this

    13 Hours 2 Minutes 48 Seconds

    where i got

    01:32:48
    OK...this seems to do it.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Row\Col
    A
    B
    C
    1
    		 10 Hours 48 Minutes 11 Seconds
    10:48:11
    		 B1 :=IFERROR(MID(0&A1,IFERROR(SEARCH("hours",0&A1),FALSE)-3,3),FALSE)*1/24+IFERROR(MID(0&A1,IFERROR(SEARCH("minutes",0&A1),FALSE)-3,3),FALSE)*1/1440+IFERROR(MID(0&A1,IFERROR(SEARCH("seconds",0&A1),FALSE)-3,3),FALSE)*1/86400
    2
    		 10 Minutes 2 Seconds
    0:10:02
    3
    		 55 Seconds
    0:00:55
    4
    		 1 Minutes 02 Seconds
    0:01:02
    5
    		 1 Hours 8 Minutes 1 Seconds
    1:08:01


    You'll need to format hh:mm:ss
    Last edited by FlameRetired; 06-10-2015 at 10:49 PM.
    Reply With QuoteRegister To Reply
  6. 06-10-2015, 11:47 PM #6
    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 from Text to Time

    Or this

    =--(IFERROR(MID(A1,FIND("H",A1)-3,2),0)&":"&IFERROR(MID(A1,FIND("M",A1)-3,2),0)&":"&IFERROR(MID(A1,FIND("S",A1)-3,2),0))

    Row\Col
    A
    B
    1
    		 10 Hours 48 Minutes 11 Seconds
    10:48:11
    2
    		 10 Minutes 12 Seconds
    00:10:12
    3
    		 55 Seconds
    00:00:55
    4
    		 12 Hours 35 Minutes 05 Seconds
    12:35:05
    5
    		 13 Hours 2 Minutes 48 Seconds
    13:02:48


    Format: Custom, hh:mm:ss
    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
+ 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. [SOLVED] Converting Text to Time
    By SimecekKJ in forum Excel General
    Replies: 5
    Last Post: 02-24-2014, 01:26 PM
  2. [SOLVED] Converting Text into Time
    By VKS in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-20-2013, 12:45 PM
  3. Converting text to time
    By rats123 in forum Excel General
    Replies: 2
    Last Post: 04-04-2011, 07:50 PM
  4. converting text value into a time value
    By maya2000 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-05-2007, 08:34 AM
  5. Converting Text to Time format of XX:XX
    By Marck in forum Excel General
    Replies: 3
    Last Post: 02-25-2006, 11:55 AM

Tags for this Thread

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