Use
=TEXT(--(LEFT(A1,LEN(A1)-1)),"0\:00\:00")+((RIGHT(A1,1)="P")/2)
and format as required
This is a discussion on Convert text string to time? within the Excel Questions forums, part of the Question Forums category; ...
Hi,
I’m 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’ll 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
Use
=TEXT(--(LEFT(A1,LEN(A1)-1)),"0\:00\:00")+((RIGHT(A1,1)="P")/2)
and format as required
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.
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
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.
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
Like this thread? Share it with others