# 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. ## 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. ## 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. ## 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)

4. ## 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. ## 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.

6. ## 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