Results 1 to 6 of 6

Convert text string to time?

This is a discussion on Convert text string to time? within the Excel Questions forums, part of the Question Forums category; Hi, I知 trying to write a formula to convert a text string to a date readable by Excel. The times ...

  1. #1
    New Member
    Join Date
    Nov 2008
    Posts
    29

    Default Convert text string to time?

    Hi,

    I知 trying to write a formula to convert a text string to a date readable by Excel.

    The times are currently in a format such as:

    82612P
    84925P
    25910A
    32911A
    35943A
    123706A
    92130P
    111747P


    As you値l see the two main problems are that the time is in 12 hour format and has an AM or PM indicator as part of the string (A or P).

    The second problem is that the hour can either be a 1 or 2 digit field (there are no leading zeros in the data).

    I have no idea how to attempt this! But I was wondering if there was some way of starting at the right and then adding separators to show which bit of the field related to PM or AM, seconds, minutes and hours.

    Any help greatly appreciated!

    Thanks,

    James

  2. #2
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    Default Re: Convert text string to time?

    Use

    =TEXT(--(LEFT(A1,LEN(A1)-1)),"0\:00\:00")+((RIGHT(A1,1)="P")/2)

    and format as required

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,576

    Default Re: Convert text string to time?

    Well I knew somebody would beat me to it, but here's mine anyway.

    =TIMEVALUE(TEXT(LEFT(A1,LEN(A1)-1), "00\:00\:00"))+TIME(--(RIGHT(A1)="P")*12,0,0)
    If posting code please use code tags.

  4. #4
    New Member
    Join Date
    Nov 2008
    Posts
    29

    Default Re: Convert text string to time?

    Amazing,

    Thank you both so much - just what I needed.

    I have no idea how these work - they just do - is it some kind of magic?!

    Thanks again,

    James

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,576

    Default Re: Convert text string to time?

    James

    That's right it's magic.

    Well it is to me half the time - I'm no formula guru as you can see from the difference in the formulas.

    If you want to find out how it works a start might be to select parts of the formula and press F4.
    If posting code please use code tags.

  6. #6
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,791

    Default Re: Convert text string to time?

    What should 123706A convert to? Both suggested formulas convert that to 12:37:06 PM........shouldn't that be AM?

    Try

    =(TEXT(LEFT(A1,LEN(A1)-1),"0\:00\:00 ")&RIGHT(A1)&"M")+0

Tags for this Thread

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
  •  


DMCA.com